当表结构在MySQL中不匹配时使用INSERT INTO SELECT

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

MySQL错误：1064 - 您的SQL语法有错误;查看与MySQL服务器版本对应的手册，以便在第1行的''附近使用正确的语法ON QUERY INSERT INTO mytable SELECT * FROM other_table ON DUPLICATE KEY UPDATE

I can't find any docs that describe this.

我找不到任何描述这个的文档。

1 个解决方案

#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部分。

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.
删除SELECT部分中列名称周围的括号。
quote the column name key, since "key" is a reserved word in MySQL.
引用列名称键，因为“key”是MySQL中的保留字。

#1