Re: [sqlite] Is this possible in SQLite?

[Dennis Cote]

John O'Neill wrote:
Hello all,
I have a fairly simple DB with two tables.  I'm trying to combine a SELECT and 
UPDATE command, if it is possible:
CREATE TABLE a (id PRIMARY KEY, data INT);
CREATE TABLE b (id INT, data INT);
INSERT INTO a VALUES( 1, 100 );
INSERT INTO b VALUES( 1, 101 );
INSERT INTO b VALUES( 1, 102 );
INSERT INTO b VALUES( 1, 103 );
...
And at some point in the future, two new tables (possibly in a different 
database) are created:
CREATE TABLE acopy (id PRIMARY KEY, data INT);
CREATE TABLE bcopy (id INT, data INT );
Is there a way to do the following:
INSERT INTO acopy SELECT * FROM a WHERE id = 1 ( SET id = some value X );
INSERT INTO bcopy SELECT * FROM b WHERE id = 1 ( SET id = X );
Instead of doing the following 4 commands or SELECTing a and b into TEMP tables:
UPDATE a SET id = X WHERE id = 1;
UPDATE b SET id = X WHERE id = 1;
INSERT INTO acopy SELECT * FROM a;
INSERT INTO bcopy SELECT * FROM b;
Thanks,
John

 

John,
The following will do what you have asked (I substituted the letter 'X' 
for your new value X for clarity), but I'm not sure if this is general 
enough for your real needs.

CREATE TABLE a (id PRIMARY KEY, data INT);
CREATE TABLE b (id INT, data INT);
INSERT INTO a VALUES( 1, 100 );
INSERT INTO b VALUES( 1, 101 );
INSERT INTO b VALUES( 1, 102 );
INSERT INTO b VALUES( 1, 103 );
CREATE TABLE acopy (id PRIMARY KEY, data INT);
CREATE TABLE bcopy (id INT, data INT );
INSERT INTO acopy SELECT 'X', data FROM a WHERE id = 1;
INSERT INTO bcopy SELECT 'X', data FROM b WHERE id = 1;
SELECT * FROM acopy;
SELECT * from bcopy;
HTH
Dennis Cote