MySQL: How to copy rows, but change a few fields?
SqlMysqlSql Problem Overview
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?
Sql Solutions
Solution 1 - Sql
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.
Solution 2 - Sql
This is a solution where you have many fields in your table and don't want to get a finger cramp from typing all the fields, just type the ones needed :)
How to copy some rows into the same table, with some fields having different values:
- Create a temporary table with all the rows you want to copy
- Update all the rows in the temporary table with the values you want
- If you have an auto increment field, you should set it to NULL in the temporary table
- Copy all the rows of the temporary table into your original table
- Delete the temporary table
Your code:
CREATE table temporary_table AS SELECT * FROM original_table WHERE Event_ID="155";
UPDATE temporary_table SET Event_ID="120";
UPDATE temporary_table SET ID=NULL;
INSERT INTO original_table SELECT * FROM temporary_table;
DROP TABLE temporary_table;
General scenario code:
CREATE table temporary_table AS SELECT * FROM original_table WHERE <conditions>;
UPDATE temporary_table SET <fieldx>=<valuex>, <fieldy>=<valuey>, ...;
UPDATE temporary_table SET <auto_inc_field>=NULL;
INSERT INTO original_table SELECT * FROM temporary_table;
DROP TABLE temporary_table
Simplified/condensed code:
CREATE TEMPORARY TABLE temporary_table AS SELECT * FROM original_table WHERE <conditions>;
UPDATE temporary_table SET <auto_inc_field>=NULL, <fieldx>=<valuex>, <fieldy>=<valuey>, ...;
INSERT INTO original_table SELECT * FROM temporary_table;
As creation of the temporary table uses the TEMPORARY
keyword it will be dropped automatically when the session finishes (as @ar34z suggested).
Solution 3 - Sql
Let's say your table has two other columns: foo and bar
INSERT INTO Table (foo, bar, Event_ID)
SELECT foo, bar, "155"
FROM Table
WHERE Event_ID = "120"
Solution 4 - Sql
If you have loads of columns in your table and don't want to type out each one you can do it using a temporary table, like;
SELECT *
INTO #Temp
FROM Table WHERE Event_ID = "120"
GO
UPDATE #TEMP
SET Column = "Changed"
GO
INSERT INTO Table
SELECT *
FROM #Temp
Solution 5 - Sql
Hey how about to copy all fields, change one of them to the same value + something else.
INSERT INTO Table (foo, bar, Event_ID)
SELECT foo, bar, Event_ID+"155"
FROM Table
WHERE Event_ID = "120"
??????????
Solution 6 - Sql
As long as Event_ID is Integer, do this:
INSERT INTO Table (foo, bar, Event_ID)
SELECT foo, bar, (Event_ID + 155)
FROM Table
WHERE Event_ID = "120"
Solution 7 - Sql
Adding to the answer by @DaveTheBFG: If you have an identity column ("Table_PK" in the below example), the INSERT line would fail, but you can do the following (SQL Server-specific, but the concept may apply to other databases):
SELECT *
INTO #Temp
FROM Table WHERE Event_ID = "120"
UPDATE #TEMP
SET Column = "Changed"
ALTER TABLE #TEMP DROP COLUMN Table_PK
EXEC sp_executesql N'INSERT INTO Table SELECT * FROM #Temp'