Re: [sqlite] copy one row to another
Hello! On Thursday 08 April 2010 01:58:09 P Kishor wrote: > The above is not going to work because I already have 649 in table t. CREATE TEMP TABLE temp_t AS SELECT * FROM t WHERE id = 651; UPDATE temp_t SET id=649; -- may be id=id-2 for set of records DELETE FROM t WHERE id = 649; INSERT INTO t SELECT * FROM temp_t; Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] copy one row to another
On Apr 8, 2010, at 4:33 AM, P Kishor wrote: > On Wed, Apr 7, 2010 at 4:24 PM, Pavel Ivanov > 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, I think this is correct. The 'on conflict' clause of a DML statement (in this case REPLACE) is not passed to virtual tables. To virtual tables, REPLACE==INSERT. Related fact: Normally, REPLACE only fires INSERT triggers. But if you set "PRAGMA recursive_triggers = ON", then it fires DELETE triggers for the rows it removes too. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] copy one row to another
On Wed, Apr 7, 2010 at 6:57 PM, Nicolas Williams wrote: > sqlite> CREATE TABLE foo(id INTEGER PRIMARY KEY, a, b, c); > sqlite> insert into foo values(1, 'a', 'b', 'c'); > sqlite> select * from foo; > 1|a|b|c > sqlite> CREATE TEMP TABLE tempfoo AS SELECT * FROM foo WHERE id = 1; > sqlite> UPDATE tempfoo SET a = 'z'; > sqlite> INSERT OR REPLACE INTO foo SELECT * FROM tempfoo; > sqlite> SELECT * FROM foo; > 1|z|b|c > sqlite> > > The trick is CREATE TEMP TABLE ... AS SELECT * FROM ... WHERE ... > followed by INSERT OR REPLACE INTO ... SELECT * FROM . Well, my problem is that INSERT OR REPLACE INTO is not working because of (possibly) FTS3. By the way, REPLACE is an alias for INSERT OR REPLACE. This is where I wish I had a way to temporarily disable TRIGGERs, via a pragma. There has to be some other way of copying the contents of one row into another... a straight-ahead update, not an insert. -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] copy one row to another
sqlite> CREATE TABLE foo(id INTEGER PRIMARY KEY, a, b, c); sqlite> insert into foo values(1, 'a', 'b', 'c'); sqlite> select * from foo; 1|a|b|c sqlite> CREATE TEMP TABLE tempfoo AS SELECT * FROM foo WHERE id = 1; sqlite> UPDATE tempfoo SET a = 'z'; sqlite> INSERT OR REPLACE INTO foo SELECT * FROM tempfoo; sqlite> SELECT * FROM foo; 1|z|b|c sqlite> The trick is CREATE TEMP TABLE ... AS SELECT * FROM ... WHERE ... followed by INSERT OR REPLACE INTO ... SELECT * FROM . ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] copy one row to another
I tried to do that, and hit walls all over the place. My solution was to import the table into OpenOffice Calc and move the columns around there. Not too elegant, but it worked. Ted On Wed, Apr 7, 2010 at 4:33 PM, P Kishor 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 > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- _ 3.14159265358979323846264338327950Let the spirit of pi 2884197169399375105820974944592307 spread all over the world! 8164062862089986280348253421170679 http://pi314.at PI VOBISCUM! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] copy one row to another
On Wed, Apr 7, 2010 at 4:32 PM, Alexey Pechnikov wrote: > Hello! > > On Thursday 08 April 2010 01:06:25 P Kishor 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. > > This work right: > > sqlite> create temp table test(id INTEGER PRIMARY KEY,a); > sqlite> insert into test (a) values (10); > sqlite> insert into test (a) values (11); > sqlite> select * from test; > 1|10 > 2|11 > sqlite> replace into test select 1,a from test where id=2; > sqlite> select * from test; > 1|11 > 2|11 > > So you have some constraints or unique indicies on your table. > > > P.S. For more than single record: > > CREATE TEMP TABLE temp_t AS > SELECT * FROM t > WHERE id = 651; > UPDATE temp_t SET id=649; -- may be id=id-2 for set of records > INSERT INTO t > SELECT * > FROM temp_t; > The above is not going to work because I already have 649 in table t. The following works, but it is crappy syntax UPDATE t SET foo = (SELECT foo FROM t WHERE id = 651) bar = (SELECT bar FROM t WHERE id = 651) qux = (SELECT qux FROM t WHERE id = 651) .. WHERE id = 649; but that would do multiple SELECTs to do a single UPDATE -- 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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] copy one row to another
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 wrote: > On Wed, Apr 7, 2010 at 4:24 PM, Pavel Ivanov 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 wrote: >>> On Wed, Apr 7, 2010 at 3:46 PM, Pavel Ivanov 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 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 >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> 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 > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] copy one row to another
On Wed, Apr 7, 2010 at 4:24 PM, Pavel Ivanov 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 wrote: >> On Wed, Apr 7, 2010 at 3:46 PM, Pavel Ivanov 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 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 >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > 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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] copy one row to another
Hello! On Thursday 08 April 2010 01:06:25 P Kishor 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. This work right: sqlite> create temp table test(id INTEGER PRIMARY KEY,a); sqlite> insert into test (a) values (10); sqlite> insert into test (a) values (11); sqlite> select * from test; 1|10 2|11 sqlite> replace into test select 1,a from test where id=2; sqlite> select * from test; 1|11 2|11 So you have some constraints or unique indicies on your table. P.S. For more than single record: CREATE TEMP TABLE temp_t AS SELECT * FROM t WHERE id = 651; UPDATE temp_t SET id=649; -- may be id=id-2 for set of records INSERT INTO t SELECT * FROM temp_t; Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] copy one row to another
On Wed, Apr 7, 2010 at 4:20 PM, Simon Slavin wrote: > > On 7 Apr 2010, at 10:06pm, P Kishor wrote: > >> On Wed, Apr 7, 2010 at 3:46 PM, Pavel Ivanov 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. > > I hope that you don't already have a record with id = 651. > Of course I have a row with id = 651. That is where I am copying the data *from* into row with id = 649. -- 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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] copy one row to another
> 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 Pavel On Wed, Apr 7, 2010 at 5:06 PM, P Kishor wrote: > On Wed, Apr 7, 2010 at 3:46 PM, Pavel Ivanov 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 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 > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] copy one row to another
On 7 Apr 2010, at 10:06pm, P Kishor wrote: > On Wed, Apr 7, 2010 at 3:46 PM, Pavel Ivanov 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. I hope that you don't already have a record with id = 651. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] copy one row to another
On Wed, Apr 7, 2010 at 3:46 PM, Pavel Ivanov 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 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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] copy one row to another
Probably the only way to do that is REPLACE INTO t (id, foo, bar, ...) SELECT 649, foo, bar, ... WHERE id = 651 Pavel On Wed, Apr 7, 2010 at 4:33 PM, P Kishor 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 > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] copy one row to another
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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
