Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2007
    Posts
    212

    Unanswered: On Duplicate Key Update Help Required

    Hi,

    I have the following table:

    product_names
    PD_ID int(10) unsigned NOT NULL,
    PD_Name varchar(400) NOT NULL,
    PRIMARY KEY (`PD_ID`,`PD_Name`),
    KEY PD_Name (`PD_Name`)



    I'm trying to run the following query but I keep getting an sql sytax error - any ideas why?

    INSERT INTO tbl_product_names (PD_ID, PD_Name)
    VALUES (SELECT tbl_products.PD_ID, tbl_supplierproducts.PD_Name FROM
    tbl_products, tbl_supplierproducts WHERE tbl_products.PD_Ref = tbl_supplierproducts.PD_Ref) ON DUPLICATE KEY UPDATE;

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The VALUES clause implies proving contants while the FROM clause implies copying elements from existing tables. You can't have both, so drop the VALUES clause and its parens and you should be closer. Without knowing what you really intended to do, this is only a guess, but it ought to get you started.

    -PatP

  3. #3
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by Pat Phelan
    The VALUES clause implies proving contants while the FROM clause implies copying elements from existing tables. You can't have both, so drop the VALUES clause and its parens and you should be closer. Without knowing what you really intended to do, this is only a guess, but it ought to get you started.

    -PatP
    I've tried removing the values clause but i still get the same error.
    Instead i've added the following to the end of the on duplicate update clause:

    on duplicate key update PD_Name=PD_Name

    It seems to work with that. I dont understand why though? I'm not sure if its producing correct results as yet - it needs some more testing.

    Basically i want to add values to the product_names table from a supplier_products table. If the name for that product is already in that table I want to simply update using the above on duplicate update clause.
    The PD_ID and PD_Name is a compound primary key in the product_nmaes table with PD_ID bieng a foriegn key from the products table.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    lets start at the beginning

    the PK is ID+Name, right?

    so if you attempt to insert another row which has the same ID+Name, what is there to update? nothing

    i think you are looking for INSERT IGNORE, not INSERT ON DUPLCIATE KEY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I'm a bit confused (partly why no-one else has mentioned)...
    But why is the primary key ID+Name?
    George
    Home | Blog

Posting Permissions

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