[RESOLVED] How to Duplicate Record In Mysql/Php

Anon

Is there a simple select or subselect statemet that will duplicate a record from within a table.

I tried some thing like this
insert into howto select * from howto where howto_id=31;

I think I am running into a problem because the howto_id is my primary key. Is there someway of doing this, simple.

I could do it with PHP by calling that record and storing the variables and then inserting without the howto_id, but that seems expensive. Seems like there should be a simple sql statement to accomplish copying a record within your table.

Anon

I don't know of any simple way, but you could use your same statement above, but list out all of the columns using NULL for the primary key (I'm assuming it's an auto_increment field).

insert into howto
select NULL, field2, field3, field4
from howto
where howto_id = 31;

gio2k

Why do you want to duplicate a record?
Sounds to me like bad design.
The purpose of designing a database is to avoid duplicate and inconsistent information.

-Gio-

amcgrath

Yep, totally agree, pus you kinda answer your own question... if you have a primary key and try to insert another row with the same pk as anohter row, well, that would mean you don't want that column as a pk....

Anon

I am actually looking for a way to do the same thing, copy a record in SQL... The primary key will be updated, however, on the duplicate record.

as for design, the case is this:
the original record is marked (by the user) to not be changed, so to change it a duplicate is created which is not marked, but is linked to the old record.

(this is in my case anyway)

gio2k

In that case, I suggest you use another table, to handle modifications of a record.
Or maybe add a field that links the record to a parent record on the same table.

-Gio

michaelphipps

I realise this was an issue from 5 years ago, but I just found this post while working on my own duplicate record issue, and wanted to add my two cents.

I think it is totally reasonable to duplicate a row in a database when you are creating a new record that is similar to the original record, but one that will be treated as a completely separate entity.

In this instance I don't believe the bad design comment is relevant:

Using the duplicate row query:
1) click copy button
2) Insert record based on data from the old record. 1 query
3) Get the last insert id
4) Make changes to the copy.

The alternative...

1) Click copy button
2) Select record from the database with a query
3) Insert record to the database with another query
4) Get the last insert id
5) Make changes to the copy.

More code is required to achieve steps 2 and 3 using the alternative, and you would have one more trip to the database. Fewer trips to the database = less load.

I'd love to hear your thoughts - I might have it totally wrong.

phenom

Not to dig up an issue from 7 years ago .... but ....

I'm storing blob data in records that i need to duplicate but change meta information about, and my simple php script doesn't seem to be working.

Is there any simple way to duplicate MySQL records? (Can I dump w/ phpMyAdmin without IF_NOT_EXISTS and AUTO_INCREMENT values, and then import that SQL file?)

phenom

phenom;10895533 wrote:
Not to dig up an issue from 7 years ago .... but ....

I'm storing blob data in records that i need to duplicate but change meta information about, and my simple php script doesn't seem to be working.

Is there any simple way to duplicate MySQL records? (Can I dump w/ phpMyAdmin without IF_NOT_EXISTS and AUTO_INCREMENT values, and then import that SQL file?)

Found a nice viable solution.

Where you have a table with a primary key you would like to duplicate content in, use this type of query:

INSERT INTO table (FOO, BAR, BOO, FAR) SELECT FOO, BAR, BOO, FAR FROM table WHERE BAR = 'whatever'

Worked perfectly for me.