AFAIK, REPLACE first tries INSERT and when it's failed it DELETEs all rows violating some constraint and then INSERTs again. I'm not sure though how it works with FTS3 table, maybe it doesn't work with virtual tables at all.
Pavel On Wed, Apr 7, 2010 at 5:33 PM, P Kishor <[email protected]> wrote: > 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 > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
