John O'Neill wrote:
Hi Dennis,
Thanks for the reply. In the original "INSERT" commands, my intention was to update a field in the columns as they were being copied to the new table. Sorry, I didn't mean just "SELECT ... WHERE id=1" as the only condition...I'd like to select those items and update their primary keys to a new value (hence the "SET" command) as they are being inserted into the new table.
So for example, in table 'a' there might be a column that has primary key = 1, and in the copied version, I want to set that primary key = 2 or some other unique value.
John,
No you can't combine SELECT and UPDATE directly.
However, the insert statements I gave do change the id from its value of 1 in table a or b to a new value of 2 in table acopy or bcopy.
INSERT INTO acopy SELECT 2, data FROM a WHERE id = 1; INSERT INTO bcopy SELECT 2, data FROM b WHERE id = 1;
Rather than selecting the entire row that matches the old id, I select the new id and the data field from the row in the old table, and then insert them into the new table.
This works for your simple example, but it may not be general enough depending upon how the values of the new id are generated.
Dennis Cote
