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

Question

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!

Answer 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;

Answer 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;

Answer 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