MySQL 如何有条件地将数据从一张表插入到另一张表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14308750/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to insert data from one table to another with conditions
提问by Ben
I am trying to insert new rows into table aand partiallyload the rows with data from table b. I would only like to insert new rows where the field b.idis not present in the a.idcolumn.
我正在尝试将新行插入table a并部分加载包含来自table b. 我只想b.id在a.id列中不存在该字段的地方插入新行。
Here are my tables:
这是我的表：
table_a table_b
--------- -----------
id id
first_name first_name
country middle_name
last_name last_name
Here is my code:
这是我的代码：
INSERT INTO table_a a
SELECT b.id, b.first_name,b. last_name
FROM table_b b WHERE b.id <> a.id
The Question:How can I do this using an insert select statement? Please note, I've included middle name in one table and Country in another table to make this more comparable to my current situation. Is there a way to "map" the fields? or must I ensure that my insert select statement has the exact same number of values to insert as the target table? Or will it just look for matching column names and only update those columns that match?
问题：如何使用插入选择语句执行此操作？请注意，我在一张表中包含了中间名，在另一张表中包含了国家/地区，以使其与我目前的情况更具有可比性。有没有办法“映射”字段？或者我必须确保我的插入选择语句具有与目标表完全相同数量的值来插入？或者它只是寻找匹配的列名并只更新那些匹配的列？
回答by John Woo
I would only like to insert new rows where the field b.id is not presentin the a.id column.
我只想插入a.id 列中不存在字段 b.id 的新行。
an alternative solution would be using LEFT JOINand IS NULL
另一种解决方案是使用LEFT JOIN和IS NULL
INSERT INTO table_a
SELECT b.*
FROM table_b b
LEFT JOIN table_a a
ON a.ID = b.ID
WHERE a.ID IS NULL
回答by rs.
You can do following, check if id exists in table_a
您可以执行以下操作，检查 table_a 中是否存在 id
INSERT INTO table_a (id, first_name, last_name)
SELECT b.id, b.first_name,b.last_name
FROM table_b b
WHERE not exists (SELECT 1 FROM table_a a WHERE b.id = a.id)
Your country column will be null for all the rows. If you want to store static value then query should be
对于所有行，您的国家/地区列将为空。如果要存储静态值，则查询应该是
INSERT INTO table_a (id, first_name, country, last_name)
SELECT b.id, b.first_name, 'USA', b.last_name
FROM table_b b
WHERE not exists (SELECT 1 FROM table_a a WHERE b.id = a.id)
回答by echo_Me
try this
尝试这个
INSERT INTO table_a a (id , first_name , last_name) Values (
SELECT b.id, b.first_name,b.last_name
FROM table_b b WHERE a.id not in (select id from table_b) )
