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

Question

I'm familiar with the following use of the command:

INSERT INTO mytable 
SELECT * 
  FROM other_table 

This works fine when the tables are identical in terms of layout.

What I'd 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 your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 ON QUERY INSERT INTO mytable SELECT * FROM other_table ON DUPLICATE KEY UPDATE

I can't find any docs that describe this.

Answer 1

Your statement is incomplete:

INSERT INTO mytable 
SELECT * 
  FROM other_table ON DUPLICATE KEY UPDATE

The syntax requires that you need to finish the UPDATE part by listing which columns to update with which values.

UPDATE:

This ought to work for your particular example:

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

The changes are:

• remove the parentheses around the column names in the SELECT part.
• quote the column name key, since "key" is a reserved word in MySQL.