www.webdeveloper.com
Results 1 to 5 of 5

Thread: Copying Rows in the same table (and modifying them) error

Hybrid View

  1. #1
    Join Date
    May 2007
    Posts
    104

    Copying Rows in the same table (and modifying them) error

    Hello, i'm having an issue with copying rows from a table and modifying and reinserting them into the same table.

    The problem is with the ID field, the data comes from a system out of my hands, and the ID field looks like this: ST3300DPAAAEAR

    Each one is unique, but i obviously can't auto-increment the field, so when copying i'm trying to add "testmarker" to each of the copied rows (see sql)

    Code:
    INSERT INTO products (
    		id,
    		ProductName,
    		Tier1, Tier2, Tier3, Tier4, Tier5, Tier6, Tier7, Tier8, Tier9
    		) SELECT
    		id = concat(id, "testmarker"),
    		ProductName,
    		Tier1, Tier2, Tier3, Tier4, Tier5, Tier6, Tier7, Tier8, Tier9
    		FROM products WHERE Tier1="Freesports"
    But i get this error: #1062 - Duplicate entry '0' for key 'PRIMARY'

    Which i assume means, trying to concatinate "testmarker" onto the ID, isn't working. Anyone have any suggestions what might be going wrong? (is this the total wrong way to do this?)

    Thank you!

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,247
    Perhaps you want to UPDATE instead of INSERT?
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    May 2007
    Posts
    104
    Hi NogDog,

    I actually want to keep the original row, but insert a copy of it, with a modified ID (as well as modifying a few other fields but that's not the issue so i didn't include it in the example.)

    So in the end i have the original and new rows in the same table, so presumably i am right in using INSERT?

  4. #4
    Join Date
    Oct 2010
    Location
    Ohio
    Posts
    233
    Don't insert the id column at all, let the database determine that by defining the column as auto_increment.
    ~Ryan
    www.rdennispallas.com <-- Personal Site, changing regularly cuz its ugly.

  5. #5
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,247
    Yeah, probably better to let it just be an auto-increment integer, and add a column you can use to point to the primary key of its "parent" record (which you'd leave null for the original records).
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles