On Wed, Apr 7, 2010 at 4:24 PM, Pavel Ivanov <[email protected]> wrote: >> I get a "Error: constraint failed". I have no constraint other than >> INTEGER PRIMARY KEY on id. > > You should have something other than integer primary key, otherwise it works: > > sqlite> create table t (id integer primary key, foo, bar); > sqlite> insert into t values (649, 'foo 1', 'bar 1'); > sqlite> insert into t values (651, 'foo 2', 'bar 2'); > sqlite> .h on > sqlite> select * from t; > id|foo|bar > 649|foo 1|bar 1 > 651|foo 2|bar 2 > sqlite> replace into t (id, foo, bar) select 649, foo, bar from t > where id = 651; > sqlite> select * from t; > id|foo|bar > 649|foo 2|bar 2 > 651|foo 2|bar 2 > >
The only other stuff going on in my db is an FTS3 virtual table with triggers that fire on update/insert/delete. Logically, that shouldn't matter, because the REPLACE should be treated as a normal UPDATE, so the FTS tables should get updated by the triggers without any problem. Maybe REPLACE gets treated as an INSERT, which would cause the constraint error (the insert_in_fts trigger would try to insert another row with the same id in the FTS tables). > Pavel > > On Wed, Apr 7, 2010 at 5:06 PM, P Kishor <[email protected]> wrote: >> On Wed, Apr 7, 2010 at 3:46 PM, Pavel Ivanov <[email protected]> wrote: >>> Probably the only way to do that is >>> >>> REPLACE INTO t (id, foo, bar, ...) >>> SELECT 649, foo, bar, ... >>> WHERE id = 651 >>> >> >> I get a "Error: constraint failed". I have no constraint other than >> INTEGER PRIMARY KEY on id. >> >>> >>> Pavel >>> >>> On Wed, Apr 7, 2010 at 4:33 PM, P Kishor <[email protected]> wrote: >>>> is there a canonical way of copying all the columns (except for the >>>> PKs, of course) from one row to another in the same table? I want to >>>> make all columns of row id = 649 in my table to become a duplicate of >>>> the values in row id = 651... of course, I want the id 649 to remain >>>> 649. >>>> >>>> UPDATE t649 >>>> SET >>>> t649.foo = t651.foo >>>> t649.bar = t651.bar >>>> .. >>>> (FROM table t649 ... ) <=== this is where I am drawing a blank >>>> >>>> >>>> -- >>>> Puneet Kishor >> _______________________________________________ >> sqlite-users mailing list >> [email protected] >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu ----------------------------------------------------------------------- Assertions are politics; backing up assertions with evidence is science ======================================================================= _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
