CODEWITHSUNDEEP
sqlmysql
Andrew
Andrew

Reputation: 238717

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

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?

Upvotes: 260

Views: 240363

Answers (8)

Whip
Whip

Reputation: 2222

If you don't mind doing it in your code, its much easier to do. For example, in php you can do

function copyQuery($table, $row){
    $queryColumns = $queryValues = '';
    foreach ($row as $key => $value) {
        $queryColumns .= $key.', ';
        $queryValues .= "'$value', ";
    }
    $queryColumns = rtrim($queryColumns, ', ');
    $queryValues = rtrim($queryValues, ', ');

    return "INSERT INTO $table ($queryColumns) VALUES ($queryValues)";
}

$records = mysqli_query($connect, "SELECT * FROM Table WHERE Event_ID = 120");
while ($row = mysqli_fetch_assoc($records)) {
    unset($row['id']);
    $row['Event_ID'] = 155;

    $query = copyQuery('Table', $row);
    mysqli_query($connect, $query);
}

Using a function is optional. I made it because I needed to do it a few times. I used this option because now I can forget about it if the database columns change in the future.

Upvotes: 0

Alex Christodoulou
Alex Christodoulou

Reputation: 2963

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:

  1. Create a temporary table with all the rows you want to copy
  2. Update all the rows in the temporary table with the values you want
  3. If you have an auto increment field, you should set it to NULL in the temporary table
  4. Copy all the rows of the temporary table into your original table
  5. 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).

Upvotes: 203

Aron
Aron

Reputation: 55

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'

Upvotes: 1

axel.becker
axel.becker

Reputation: 41

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"

Upvotes: 4

Davethebfg
Davethebfg

Reputation: 203

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

Upvotes: 12

Dimitar
Dimitar

Reputation: 85

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"

??????????

Upvotes: 6

dcp
dcp

Reputation: 55444

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.

Upvotes: 364

Peter Bailey
Peter Bailey

Reputation: 105888

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"

Upvotes: 53

Related Questions