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