Replace some rows in one table with rows of another table

Question

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.

• RDFozz: 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.
• Rick James: Might you have rows in table2 that do not match rows in table1? What should be done? Insert such "new" rows? Or ignore them?
• porton: @RickJames Not important for me.

Answer 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.

• porton: 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
• Akina: @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.
• porton: But can it be done without both: programming (e.g. to enumerate all column names) and typing all column names manually?
• Akina: @porton There is a way (answer updated), but it can be not safe.
• Rick James: @porton - See the JOIN ... USING(...) syntax.

Answer 2

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.