copy one row to another

[P Kishor]

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

[Pavel Ivanov]

Probably the only way to do that is

REPLACE INTO t (id, foo, bar, ...)
SELECT 649, foo, bar, ...
WHERE id = 651


Pavel

Post by P Kishor
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
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[P Kishor]

Post by Pavel Ivanov
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.

Post by Pavel Ivanov
Pavel

Post by P Kishor
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

[Simon Slavin]

Post by P Kishor

Post by Pavel Ivanov
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.

[P Kishor]

Post by Simon Slavin

Post by P Kishor

Post by Pavel Ivanov
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.

[Pavel Ivanov]

Post by P Kishor
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

Post by P Kishor

Post by Pavel Ivanov
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.

Post by Pavel Ivanov
Pavel

Post by P Kishor
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
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[P Kishor]

Post by Pavel Ivanov

Post by P Kishor
I get a "Error: constraint failed". I have no constraint other than
INTEGER PRIMARY KEY on id.

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).

Post by Pavel Ivanov
Pavel

Post by P Kishor

Post by Pavel Ivanov
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.

Post by Pavel Ivanov
Pavel

Post by P Kishor
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
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Pavel Ivanov]

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

Post by P Kishor

Post by Pavel Ivanov

Post by P Kishor
I get a "Error: constraint failed". I have no constraint other than
INTEGER PRIMARY KEY on id.

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).

Post by Pavel Ivanov
Pavel

Post by P Kishor

Post by Pavel Ivanov
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.

Post by Pavel Ivanov
Pavel

Post by P Kishor
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
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
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
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Dan Kennedy]

Post by P Kishor

Post by Pavel Ivanov

Post by P Kishor
I get a "Error: constraint failed". I have no constraint other than
INTEGER PRIMARY KEY on id.

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.

[Alexey Pechnikov]

Hello!

Post by P Kishor

Post by Pavel Ivanov
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/

[P Kishor]

Post by Alexey Pechnikov
Hello!

Post by P Kishor

Post by Pavel Ivanov
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.

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.
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

[Alexey Pechnikov]

Hello!

Post by P Kishor
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/

[Ted Rolle]

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

Post by P Kishor
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
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Nicolas Williams]

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 <temp table>.

[P Kishor]

On Wed, Apr 7, 2010 at 6:57 PM, Nicolas Williams

Post by Nicolas Williams
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 <temp table>.

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.