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

mysql sql loops insert
acme picture acme · Mar 9, 2011 · Viewed 69.4k times · Source

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

krtek picture krtek · Mar 9, 2011

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;

Related questions

SQL - Looping through ever row of table in MySQL?

So I have 2 tables, communication,and movement. communication has columns fromID,timestamp that has ID of caller, and time the call was made. Then I have another table movement that has ID,timestamp,x,y, that has the ID of …

Read More
How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More
How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's …

Read More