Alex Rivera | Logout

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

Asked 2011-03-09T14:52:35.687
87

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!

mysql sql loops insert
Edit
Report

2 Answers

173

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;
answered 2011-03-09T14:55:23.930
7
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;
answered 2011-03-09T14:58:30.247

Your Answer