Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Apr 2008
    Posts
    36

    Talking Unanswered: 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 04:43.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    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?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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 */
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    But why..?
    George
    Home | Blog

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    reasons why: let me show u them

    1. because the original poster asked for it
    2. see 1.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    "u" ?
    EDIT: Mr
    George
    Home | Blog

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    george, google this as a phrase: "let me show you it"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    ...something to do with roflcats...?

    I still don't geddit
    George
    Home | Blog

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that is right, the http://icanhascheezburger.com lolcats
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    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 15:02.

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

  13. #13
    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:
    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:
    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 18:57.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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 20:47.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    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.
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •