MySQL: 如何在 SQL 查询中为每个结果插入记录？

假设我有一个选择

SELECT DISTINCT id, customer_id, domain FROM config WHERE type = 'foo';

返回一些记录。

如何对结果集中的 reach 行执行插入操作，如下所示

INSERT INTO config (id, customer_id, domain) VALUES (@id, @customer_id, 'www.example.com');

其中 @id和 @customer_id是结果集中行的字段？

Edit: 我不想只是复制它，而是在字段 domain中插入一个新值。尽管如此，一个手掌的情况，因为它是显而易见的容易; ——谢谢！

74836 次浏览

小开

最佳答案

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 :

INSERT INTO config (id, customer_id, domain)
SELECT DISTINCT id, customer_id, 'www.example.com' FROM config;

小开

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:

http://dev.mysql.com/doc/refman/5.1/en/insert-select.html

小开

EDIT- After reading comment on @Krtek's answer.

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.

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

小开

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;

小开

Execute this SQL statement:

-- 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.

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.