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, 2018 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, 2018 at 23:59
  • @RickJames Not important for me.
    – porton
    Nov 28, 2018 at 11:52

2 Answers 2

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
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, 2018 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, 2018 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, 2018 at 17:45
  • @porton There is a way (answer updated), but it can be not safe.
    – Akina
    Nov 27, 2018 at 18:40
  • @porton - See the JOIN ... USING(...) syntax.
    – Rick James
    Nov 28, 2018 at 0:00

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 and acknowledge you have read our privacy policy.

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