MySQL：如何在 SQL 查询中为每个结果插入一条记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5247530/
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
MySQL: How to insert a record for each result in a SQL query?
提问by acme
Say I have a select
说我有一个选择
SELECT DISTINCT id, customer_id, domain FROM config WHERE type = 'foo';
which returns some records.
它返回一些记录。
How can I do an insert for reach row in the result set like
我怎样才能在结果集中为到达行做一个插入，比如
INSERT INTO config (id, customer_id, domain) VALUES (@id, @customer_id, 'www.example.com');
where @idand @customer_idare the fields of the row in the result set?
其中@id和@customer_id是该行的结果集中的领域？
edit: I didn't want to just duplicate it, but insert a new value in the field domaininstead. Nevertheless a facepalm-situation as it's plain easy ;-) Thanks!
编辑：我不想只是复制它，而是在该字段中插入一个新值domain。尽管如此，一个facepalm-situation因为它很容易;-)谢谢！
回答by krtek
As simple as this :
就这么简单：
INSERT INTO config (id, customer_id, domain)
SELECT DISTINCT id, customer_id, domain FROM config;
If you want "www.example.com" as the domain, you can do :
如果你想要“ www.example.com”作为域，你可以这样做：
INSERT INTO config (id, customer_id, domain)
SELECT DISTINCT id, customer_id, 'www.example.com' FROM config;
回答by Ken
INSERT INTO config (id, customer_id, domain)
SELECT id, customer_id, 'www.example.com' FROM (
SELECT DISTINCT id, customer_id, domain FROM config
WHERE type = 'foo'
) x;
回答by Jeff Fritz
INSERT INTO Config (id, customer_id, domain)
SELECT DISTINCT id, customer_id, 'www.example.com' FROM config
The MySQL documentation for this syntax is here:
此语法的 MySQL 文档在这里：
回答by Sachin Shanbhag
EDIT- After reading comment on @Krtek's answer.
编辑- 阅读对@Krtek 回答的评论后。
I guess you are asking for an update instead of insert -
我猜你是要求更新而不是插入 -
update config set domain = 'www.example.com'
This will update all existing records in config table with domain as 'www.example.com' without creating any duplicate entries.
这将更新配置表中域为“www.example.com”的所有现有记录，而不会创建任何重复条目。
OLD ANSWER-
旧答案-
you can use something like -
你可以使用类似的东西 -
INSERT INTO config (id, customer_id, domain)
select id, customer_id, domain FROM config
Note:- This will not work if you have id as primary key
注意：- 如果您将 id 作为主键，这将不起作用
回答by Mike Sherrill 'Cat Recall'
Execute this SQL statement:
执行此 SQL 语句：
-- Do nothing.
You want to select distinct rows from "config", and insert those same rows into the same table. They're already in there. Nothing to do.
您想从“config”中选择不同的行，并将这些相同的行插入到同一个表中。他们已经在那里了。没事做。
Unless you actually just want to update some or all of the values in the "domain" column. That would require an UPDATE statement that really did something.
除非您实际上只想更新“域”列中的部分或全部值。这将需要一个真正起作用的 UPDATE 语句。
