MySQL: How to insert a record for each result in a

问题:

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 @id and @customer_id are the fields of the row in the result set?

edit: I didn't want to just duplicate it, but insert a new value in the field domain instead. Nevertheless a facepalm-situation as it's plain easy ;-) Thanks!

回答1:

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;

回答2:

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;

回答3:

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

回答4:

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

回答5:

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.