Copy row but with new id

Question

I have a table "test" with an auto incremented id and an arbitrary number of columns.

I want to make a copy of a row in this table with all columns the same except for the id of course.

Is there a way to do this without naming all columns?

I thought INSERT... SELECT... ON DUPLICATE KEY would help me until I realised that it never makes an INSERT ON DUPLICATE, it just updates the existing row.

Duplicate of : http://stackoverflow.com/questions/729489/duplicate-copy-records-in-the-same-mysql-table ? — JohnP, Jul 04 '12 at 15:09
@JohnP No, it is not a duplicate. OP don't want to use `ON DUPLICATE ...` and the referred post uses `update`, which OP don't want. — Ravinder Reddy, Jul 04 '12 at 15:14
@Ravinder - Where does the OP say he don't want update? The referred post is exactly what he wants to do. Make a copy of a row WITHOUT NAMING ALL COLUMNS. — JohnP, Jul 04 '12 at 15:20
@JohnP OP says "*... until i realised that it never makes an insert on duplicate, it just updates the existing row.*" — Ravinder Reddy, Jul 04 '12 at 15:25
And, if you actually read the referred post, the second question is the answer, using a temporary table. You can't do it without naming all columns otherwise, which the OP doesn't want. temp table, insert row, update id, reinsert into first table. — JohnP, Jul 04 '12 at 15:26
@Martin @JohnP OK. Problem is with `auto_increment`. Updating an id in tmp table may not be useful when copied to main table. Because updated pk field value might already be present in the main table. [Tim Ruehsen](http://stackoverflow.com/a/8077952/767881)'s answer, in the referred post, best suits for this requirement. — Ravinder Reddy, Jul 04 '12 at 15:42

score 119 · Accepted Answer · edited Dec 24 '22 at 02:06

119

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;

edited Dec 24 '22 at 02:06

starball

20,030
7
43
238

answered Jul 04 '12 at 15:09

Ravinder Reddy

23,692
6
52
82

13

But here you're saying what columns to copy. I wan't to do it by only knowing the name of the primary key. – Martin Jul 04 '12 at 15:22
This is very correct. When you create table like this "CREATE TEMPORARY TABLE tmp SELECT * from my_table WHERE ...;", it does not create primary keys as well as other keys, and it does not fill the "AUTO_INCREMENT" value for this temp table. And it does not do fields auto incremental. However it does save the nullable param for the field and that is why it is better to drop it. – Yevgeniy Afanasyev May 28 '15 at 01:34
3

**BIG WARNING!!** The `ALTER TABLE` causes an implicit transaction commit, even though it's on a temporary table. And, actually, you should do `DROP TEMPORARY TABLE tmp;` to avoid the same on the final line, though it's too late by then. – Lightness Races in Orbit Jan 25 '16 at 17:25
What if we want to add custom col value with other values copied from previous row? – Manish Kumar May 17 '19 at 07:56
@CrackIt: the above thought process is only to copy records without mentioning column names or expressions. for custom column values you however need to use column names. – Ravinder Reddy May 18 '19 at 08:15

score 7 · Answer 2 · edited Jul 08 '15 at 16:03

7

This works in MySQL all versions and Amazon RDS Aurora:

INSERT INTO my_table SELECT 0,tmp.* FROM tmp;

or

Setting the index column to NULL and then doing the INSERT.

But not in MariaDB, I tested version 10.

edited Jul 08 '15 at 16:03

rink.attendant.6

44,500
61
101
156

answered Jul 08 '15 at 15:54

rcalvachi

71
1
1

Could that be because you set the column to `0`, not `NULL`? I'd like to know how to make this work portably. – Lightness Races in Orbit Jan 21 '16 at 11:47
I tested with MariaDB and the above doesn't work with it. I don't know the details why it doesn't. But it is definitely because of the 0 or setting the column to NULL. I also didn't find any documented issue for it. – rcalvachi Jan 22 '16 at 15:53
I'm asking whether you tested `NULL` as well as `0`. – Lightness Races in Orbit Jan 22 '16 at 15:56
Yes, I tested both 0 and NULL. – rcalvachi Jan 25 '16 at 17:19
OK - that's a concern as we are thinking of "upgrading" to MariaDB from MySQL and rely on this behaviour :( – Lightness Races in Orbit Jan 25 '16 at 17:48
I hear you. I stopped my upgrade to MariaDB because of that reason too. – rcalvachi Jan 26 '16 at 18:47
2

For this solution, can I specify a row to duplicate by adding `WHERE ID=123` at the end? (or `tmp` is a dupe of my desired row?) – beroe Apr 29 '20 at 23:40

score 4 · Answer 3 · edited Apr 05 '17 at 08:37

THIS WORKS FOR DUPLICATING ONE ROW ONLY

Select your ONE row from your table
Fetch all associative
unset the ID row (Unique Index key)
Implode the array[0] keys into the column names
Implode the array[0] values into the column values
Run the query

The code:

 $qrystr = "SELECT * FROM mytablename  WHERE id= " . $rowid;
 $qryresult = $this->connection->query($qrystr);
 $result = $qryresult->fetchAll(PDO::FETCH_ASSOC);
 unset($result[0]['id']); //Remove ID from array
 $qrystr = " INSERT INTO mytablename";
 $qrystr .= " ( " .implode(", ",array_keys($result[0])).") ";
 $qrystr .= " VALUES ('".implode("', '",array_values($result[0])). "')";
 $result = $this->connection->query($qrystr);
 return $result;

Of course you should use PDO:bindparam and check your variables against attack, etc but gives the example

additional info

If you have a problem with handling NULL values, you can use following codes so that imploding names and values only for whose value is not NULL.

foreach ($result[0] as $index => $value) {
    if ($value === null) unset($result[0][$index]);
}

This implies the use of PHP and this question is about MySQL by itself — PhoneixS, Jun 05 '15 at 11:41

score 2 · Answer 4 · answered Jun 26 '20 at 11:58

SET @table = 'the_table';
SELECT GROUP_CONCAT(IF(COLUMN_NAME IN ('id'), 0, CONCAT("\`", COLUMN_NAME, "\`"))) FROM INFORMATION_SCHEMA.COLUMNS
                  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = @table INTO @columns;
SET @s = CONCAT('INSERT INTO ', @table, ' SELECT ', @columns,' FROM ', @table, ' WHERE id=1');
PREPARE stmt FROM @s;
EXECUTE stmt;

score 1 · Answer 5 · answered Apr 25 '19 at 11:40

depending on how many columns there are, you could just name the columns, sans the ID, and manually add an ID or, if it's in your table, a secondary ID (sid):

insert into PROG(date, level, Percent, sid) select date, level, Percent, 55 from PROG where sid = 31 Here, if sid 31 has more than one resultant row, all of them will be copied over to sid 55 and your auto iDs will still get auto-generated. for ID only: insert into PROG(date, level, Percent, ID) select date, level, Percent, 55 from PROG where ID = 31 where 55 is the next available ID in the table and ID 31 is the one you want to copy.

score 1 · Answer 6 · answered May 09 '22 at 11:31

1

I am using a temporary table:

CREATE TEMPORARY TABLE tmp SELECT * FROM sitelog WHERE 1=1;
ALTER TABLE tmp DROP COLUMN `ID`;
INSERT INTO sitelog SELECT 0, tmp.* FROM tmp;
DROP TEMPORARY TABLE tmp;

answered May 09 '22 at 11:31

Talel

101
1
3

score -1 · Answer 7 · edited Mar 07 '16 at 10:32

INSERT into table_name (  
    `product_id`, 
    `other_products_url_id`, 
    `brand`, 
    `title`, 
    `price`, 
    `category`, 
    `sub_category`, 
    `quantity`, 
    `buy_now`, 
    `buy_now_url`, 
    `is_available`, 
    `description`, 
    `image_url`, 
    `image_type`, 
    `server_image_url`, 
    `reviews`, 
    `hits`, 
    `rating`, 
    `seller_name`, 
    `seller_desc`, 
    `created_on`, 
    `modified_on`, 
    `status`) 
SELECT 
    `product_id`, 
    `other_products_url_id`, 
    `brand`, 
    `title`, 
    `price`, 
    `category`, 
    `sub_category`, 
    `quantity`, 
    `buy_now`, 
    concat(`buy_now_url`,'','#test123456'), 
    `is_available`, 
    `description`, 
    `image_url`, 
    `image_type`, 
    `server_image_url`, 
    `reviews`, 
    `hits`, 
    `rating`, 
    `seller_name`, 
    `seller_desc`, 
    `created_on`, 
    `modified_on`, 
    `status` 
FROM `table_name` WHERE id='YourRowID';

Your formatting is fine. The only problem with your answer is that the question asks for a way to do this **without** naming all the columns... — rodrigo-silveira, Apr 18 '16 at 12:53

Copy row but with new id

7 Answers7

THIS WORKS FOR DUPLICATING ONE ROW ONLY

Linked