MySQL How to copy rows but change a few fields

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  lt conditions gt    UPDATE temporary table SET  lt fieldx gt   lt valuex gt    lt fieldy gt   lt valuey gt         UPDATE temporary table SET  lt auto inc field gt  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  lt conditions gt    UPDATE temporary table SET  lt auto inc field gt  NULL   lt fieldx gt   lt valuex gt    lt fieldy gt   lt valuey gt         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

User · Answer

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

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

Examples related to sql

Examples related to mysql