Results 1 to 2 of 2

Thread: Copying Rows in the same table (changing the unique ID while doing so) Not working!

Hybrid View

  1. #1
    Join Date
    Mar 2010
    Posts
    2

    Unhappy Copying Rows in the same table (changing the unique ID while doing so) Not working!

    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 (i think), 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, so the ID would end up looking like this for example: "ST3300DPAAAEARtestmarker" (see below)

    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
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,922
    Try using single quote and different column name for concat output

    INSERT INTO products (
    id,
    ProductName,
    Tier1, Tier2, Tier3, Tier4, Tier5, Tier6, Tier7, Tier8, Tier9
    ) SELECT
    id1 = concat(id, 'testmarker'),
    ProductName,
    Tier1, Tier2, Tier3, Tier4, Tier5, Tier6, Tier7, Tier8, Tier9
    FROM products WHERE Tier1='Freesports'

Posting Permissions

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