Using INSERT INTO SELECT when table structures do not match in MySQL

Question

I know the following use of the command:

INSERT INTO mytable 
SELECT * 
  FROM other_table 

This works great when the tables are identical in layout.

What I would like to do is something like:

INSERT INTO mytable 
SELECT * 
  FROM other_table ON DUPLICATE KEY UPDATE

This fails with a syntax error:

MySQL Error: 1064 - You have an error in the SQL syntax; check the manual that matches your MySQL server version for the correct syntax to use next to '' on line 1 ON QUERY INSERT INTO mytable SELECT * FROM other_table ON DUPLICATE KEY UPDATE

I can not find any documents that describe this.

Answer 1

Your expression is incomplete:

INSERT INTO mytable 
SELECT * 
  FROM other_table ON DUPLICATE KEY UPDATE

, UPDATE, , , .

UPDATE:

:

INSERT INTO mytable2 (id, name, `key`)
  SELECT id, name, `key` FROM mytable b
ON DUPLICATE KEY UPDATE name = b.name

:

• SELECT.
• key, "" MySQL.