acme acme - 4 months ago 10
SQL Question

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!

Answer

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;