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