在 MySQL 中使用 INSERT ... SELECT 添加额外的列值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5584588/
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
Adding an extra column value with INSERT ... SELECT in MySQL
提问by Anthony
I have two tables and my goal is to move specific data from the first table into the second table along with a reason for why that data was moved. For instance:
我有两个表，我的目标是将特定数据从第一个表移动到第二个表中，并说明移动该数据的原因。例如：
raw_data_table
SELECT * FROM raw_data_table where id IS NULL;
I would want to move this into the second table, which is identical to the first table except for an extra column reason. I tried:
我想把它移到第二个表中，除了一个额外的列之外，它与第一个表相同reason。我试过：
INSERT INTO bad_data_table
(SELECT * FROM raw_data_table WHERE id IS NULL), "The ID is NULL";
But this returns a syntax error. How can I copy the entire row over and add the reason value?
但这会返回语法错误。如何复制整行并添加原因值？
回答by Cade Roux
INSERT INTO bad_data_table
SELECT *, 'The ID is NULL' AS Reason
FROM raw_data_table
WHERE id IS NULL;
回答by Khez
Try this query:
试试这个查询：
INSERT INTO bad_data_table VALUES (
(SELECT * FROM raw_data_table WHERE id IS NULL LIMIT 1), "The ID is NULL");
The subquery here needs to have 1 row!
这里的子查询需要有 1 行！
