| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
|
05-26-08, 03:15
|
|
Registered User
|
|
Join Date: Apr 2008
Posts: 36
|
|
In PHPMyAdmin, how to duplicate a record in a table?
|
|
In PHPMyAdmin, how can I duplicate a record in a table?
|
Last edited by Leafgreen; 05-26-08 at 03:43.
|
05-26-08, 05:54
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
|
|
off the top of my head, you can't
a table should have a primary key, and a primary key is by definition unique
therefore you can't duplicate an entire "record"
(note: in relational databases, they are rows, not records)
if you want some other variant of duplication, you will have to give us some more specifics
|
|
05-26-08, 14:09
|
|
Registered User
|
|
Join Date: Apr 2008
Posts: 36
|
|
|
|
Hi r937 and Thank you for your reply. I see what you mean about the primary key. Ok, then if you wanted to duplicate all the information in a row, and make a new row except with an incremented value for the primary key, how would you do that?
|
|
05-26-08, 15:19
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
|
|
i would do it like this:
Code:
INSERT
INTO daTable
( list all columns except auto_increment )
SELECT list all columns except auto_increment
FROM daTable
WHERE id = nnn /* where nnn is the id of the row i want to copy */
|
|
05-26-08, 16:35
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,156
|
|
|
|
05-26-08, 16:44
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
|
|
reasons why: let me show u them
1. because the original poster asked for it
2. see 1.
|
|
05-26-08, 18:07
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,156
|
|
"u" ?
EDIT: Mr 
|
|
05-26-08, 18:39
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
|
|
george, google this as a phrase: "let me show you it"
|
|
05-26-08, 18:49
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,156
|
|
...something to do with roflcats...?
I still don't geddit 
|
|
05-26-08, 18:55
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
|
|
|
|
05-27-08, 13:45
|
|
Registered User
|
|
Join Date: Apr 2008
Posts: 36
|
|
George: I guess I am missing something based on your response, "But why?" My frequent scenario is that I have a table with about 30 columns, and each cell has a significant amount of data. But sometimes, I need to add a row with very similar data as an existing row. So the quickest way to add a row with mostly the same data is to
1. duplicate it with an incremental primary key, then
2. make the minor change(s) in the new row's cells as I wish.
This is much quicker than adding a new, empty row and then re-typing or copying and pasting into each of the 30 cells. Geddit? What would you do in this scenario?
Rudy: thanks for your answer.
|
Last edited by Leafgreen; 05-28-08 at 14:02.
|
05-28-08, 17:12
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,156
|
|
Dear me, appear to have missed this here thread.
I'm glad you've come up with a justification for doing this - my "but why?" was aimed at finding this out exactly!
Call my a cynic, but generally when people ask this it is because their design is flawed - 99% of the time duplicate data is redundant data.
And anyhow, I would still approach it wish a single insert which included the minor changes, rather than running >1 query to produce the record.
If you simply want to duplicate with the new seed - Rudy's response will give you that.
P.S. Rudy, thanks for the PM - it did make me chuckle 
|
|
05-25-09, 17:50
|
|
Registered User
|
|
Join Date: Apr 2008
Posts: 36
|
|
Hello again. Well I finally used this. Here's what I ran in the SQL editor of the MySQL-Front app:
Quote:
INSERT
INTO tbl_adfer
'ad_off_name','active_a','ad_ofr_nae'
SELECT
'ad_off_name','active_a','ad_ofr_nae'
FROM tbl_adfer
WHERE id = 23748
|
And here's the error message I got:
Quote:
SQL execution error # 1064. Response from the database:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ad_off_name','active_a','ad_ofr_nae' at line 3
|
23748 is the value of the auto_increment and primary key column for the row to be duplicated. There are no typos in the column labels, since didn't re-type anything. I exported, coverted tabs to ',' and pasted into the query.
|
Last edited by Leafgreen; 05-25-09 at 17:57.
|
05-25-09, 19:44
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
|
|
well, you musta done something else
the syntax for the INSERT statement begins with INSERT INTO and then the table name
then comes an optional list of column names which designate the columns that you're inserting into
if the list is omitted, there must be a value provided for every column in the table
if the list is included, it must --
1. be enclosed in parentheses, and
2. contain a list of column names, not strings
that is where your query died
see also post #4 in this thread
|
Last edited by r937; 05-25-09 at 19:47.
|
05-25-09, 20:08
|
|
Registered User
|
|
Join Date: Apr 2008
Posts: 36
|
|
I didn't have the parentheses around the list in line 3. So I added and got the same error.
Quote:
|
2. contain a list of column names, not strings
|
What's the difference? Are they strings the way I have them? I don't understand.
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
quick question...answer you know off top of your head
