MySQL: How to insert a record for each result in a SQL query?

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 facepa开发者_如何学Pythonlm-situation as it's plain easy ;-) Thanks!

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 id, customer_id, 'www.example.com' FROM (
  SELECT DISTINCT id, customer_id, domain FROM config
  WHERE type = 'foo'
) x;

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

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.