0

I have two tables and a set of primary key values. I want to replace all rows of the first table whose primary keys are in this set with the corresponding (having the same primary key) rows of the second table.

(The two tables have the same structure.)

How to do this with MySQL and PHPMyAdmin or (worse) command line?

You can assume that both tables have a row for every value of the primary keys in the set.

  • Can you delete the rows in table2 with the target primary keys, or do you have foreign key relationships that would prevent that? If the tables have literally the same structure (same columns, same order), and you can set primary key values, then you could DELETE the target rows, and the INSERT them, which may let you skip specifying the columns. – RDFozz Nov 27 '18 at 18:34
  • Might you have rows in table2 that do not match rows in table1? What should be done? Insert such "new" rows? Or ignore them? – Rick James Nov 27 '18 at 23:59
  • @RickJames Not important for me. – porton Nov 28 '18 at 11:52
1
UPDATE table1, table2
SET table1.field1 = table2.field1,
    table1.field2 = table2.field2,
--  .....   
    table1.fieldN = table2.fieldN
WHERE table1.id = table2.id
  AND table1.id IN ('id1', 'id2', /* ... */ 'idN' )

Or you can use

REPLACE table1
SELECT * 
FROM table2
WHERE table1.id IN ('id1', 'id2', /* ... */ 'idN' )

but it can give undesired interferention if there is any additional unique index except primary one.

  • 1. The list of columns is very long, I don't want to type the entie table1.X = table2.X list. 2. Your operator does not restrict to certain set of primary keys as I requested in my question (however, I know how to do this). I want a better answer – porton Nov 27 '18 at 17:31
  • @porton 1) What's the point of saving bytes? Anyway, the short query text which uses asterisk sign (or without fields spec at all) will be converted into a field enumeration by the server before execution. The query text can be simply build programmatically. 2) Add proper AND tableX.id IN (id's list) to WHERE clause. – Akina Nov 27 '18 at 17:37
  • But can it be done without both: programming (e.g. to enumerate all column names) and typing all column names manually? – porton Nov 27 '18 at 17:45
  • @porton There is a way (answer updated), but it can be not safe. – Akina Nov 27 '18 at 18:40
  • @porton - See the JOIN ... USING(...) syntax. – Rick James Nov 28 '18 at 0:00
0

We can use mysqldump --replace --where "..." db table. It will output the REPLACE statement. It remains to edit this REPLACE to change the table name and then execute this SQL code.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.