CODEWITHSUNDEEP
mysqlselect
joshwbrick
joshwbrick

Reputation: 5992

MySQL: Update all Columns With Values From A Separate Table

Sometimes if I want to quickly copy records from one table to another (that has the same structure) I use a query like this:

INSERT INTO table2 SELECT * FROM table1 WHERE id = SOME_VALUE

How can I add a ON DUPLICATE KEY UPDATE to this statement? I tried this:

INSERT INTO SELECT * FROM table1 WHERE id = 1 ON DUPLICATE KEY UPDATE SELECT * FROM table1 WHERE id = 1

But I get an error. Is there away to accomplish the query above with out individually listing each column in the query?

P.S. Yes, I realize that it is not good practice to have multiple tables with identical structures, but sometimes you just don't get control over everything in the workplace!

Upvotes: 0

Views: 3714

Answers (2)

Phill Pafford
Phill Pafford

Reputation: 85308

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html Just use the SELECT field_name from the other table like in dnagirls example

Upvotes: 1

dnagirl
dnagirl

Reputation: 20456

The below UPDATES if there is no PK duplication and INSERTs is there is:

REPLACE INTO table2(field1, field2, field3)
SELECT field1, field2,field3 FROM table1
WHERE id=1;

Upvotes: 1

Related Questions