How to Copy a Record in a SQL Table But Swap Out the Unique Id of the New Row

How do you copy a record in a SQL table but swap out the unique id of the new row?

Try this:

insert into MyTable(field1, field2, id_backup)
    select field1, field2, uniqueId from MyTable where uniqueId = @Id;

Any fields not specified should receive their default value (which is usually NULL when not defined).

How to copy a row and insert in same table with a autoincrement field in MySQL?

Use INSERT ... SELECT:

insert into your_table (c1, c2, ...)
select c1, c2, ...
from your_table
where id = 1

where c1, c2, ... are all the columns except id. If you want to explicitly insert with an id of 2 then include that in your INSERT column list and your SELECT:

insert into your_table (id, c1, c2, ...)
select 2, c1, c2, ...
from your_table
where id = 1

You'll have to take care of a possible duplicate id of 2 in the second case of course.

Copy row but with new id

Let us say your table has following fields:

( pk_id int not null auto_increment primary key,
  col1 int,
  col2 varchar(10)
)

then, to copy values from one row to the other row with new key value,
following query may help

insert into my_table( col1, col2 ) select col1, col2 from my_table where pk_id=?;

This will generate a new value for pk_id field and copy values from col1, and col2 of the selected row.

You can extend this sample to apply for more fields in the table.

UPDATE:

In due respect to the comments from JohnP and Martin -

We can use temporary table to buffer first from main table and use it to copy to main table again.
Mere update of pk reference field in temp table will not help as it might already be present in the main table. Instead we can drop the pk field from the temp table and copy all other to the main table.

With reference to the answer by Tim Ruehsen in the referred posting:

CREATE TEMPORARY TABLE tmp SELECT * from my_table WHERE ...;
ALTER TABLE tmp drop pk_id; # drop autoincrement field
# UPDATE tmp SET ...; # just needed to change other unique keys
INSERT INTO my_table SELECT 0,tmp.* FROM tmp;
DROP TEMPORARY TABLE tmp;

Hope this helps.

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

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.

How to copy a row from one SQL Server table to another

As long as there are no identity columns you can just

INSERT INTO TableNew
SELECT * FROM TableOld
WHERE [Conditions]

Copy rows from the same table and update the ID column

INSERT INTO ProductDefinition (ProdID, Definition, Desc)
SELECT
  xxx, Definition, Desc
FROM
  ProductDefinition
WHERE
  ProdID = yyy

The xxx is your new ProdID and the yyy is your old one. This also assumes that DefID is automagically populated on INSERT.

copy and insert row in same table with new primary key+php

Because you're trying to insert every field in that row, which includes the primary key. If you want to specify a subset of fields, specify a subset of fields:

INSERT INTO messages (ColumnA, ColumnB, Etc)
SELECT ColumnA, ColumnB, Etc
FROM messages WHERE id='$id'

That way you're not also inserting a value into id.

(This is of course assuming that the database auto-generates the primary key. If it doesn't, you'd need to insert whatever value would be required for a primary key.)

---

As an aside, the use of what appears to be a PHP variable (and explicit quotes) in your SQL code strongly implies that you are likely vulnerable to SQL injection. Right now is the best time to correct that.

---

Related Topics