On Wed, Apr 7, 2010 at 4:32 PM, Alexey Pechnikov <pechni...@mobigroup.ru> 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
