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