update with join

[mrhankey]

hi there,

I have tried numerous queries on my database but 0 rows affected.

Basically the Documents table has the ProductID's held in here.

so say they have 100 docs for a Product it would record the ProductID for in every Document entry.
This works fine.

issue is now we need to add in the productID into folders table as we now display folders for a product and then the documents beneath the folder. again query works but I need to update the new columns added in folders table to match the product ids from products table.

this runs but says 0 rows affected even though there are matches in the tables.

query I tried:

 update folders 
 join Documents on 
 Documents.FolderID = folders.FolderID 
 set folders.folder_ProductID = Documents.products_ProductID 

anyone see why it is not adding in the products ids associated with the documents to the folder entry?

many thanks

[mrhankey]

i am guessing it needs to be set in a loop as the query returns more than one results but no idea how to loop this query?

thanks

[rch1231]

You can't use join in an update. You have to reference both tables and select the records to update.

update folders, Documents
set folders.folder_ProductID = Documents.products_ProductID 
where folders.FolderID = Documents.FolderID