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;

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.

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

I'm a bit confused (partly why no-one else has mentioned)...
But why is the primary key ID+Name?