MySQL: How to copy rows, but change a few fields?

sql mysql
Andrew picture Andrew · May 6, 2010 · Viewed 166.7k times · Source

I have a large number of rows that I would like to copy, but I need to change one field.

I can select the rows that I want to copy:

select * from Table where Event_ID = "120"

Now I want to copy all those rows and create new rows while setting the Event_ID to 155. How can I accomplish this?

Answer

dcp picture dcp · May 6, 2010 
INSERT INTO Table
          ( Event_ID
          , col2
           ...
          )
     SELECT "155"
          , col2
           ...
      FROM Table WHERE Event_ID = "120"

Here, the col2, ... represent the remaining columns (the ones other than Event_ID) in your table.

Related questions

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
What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN in MySQL?

Read More