MySQL Update Troubles

[Schmotty]

For the life of me I can not figure out what is going on. One UPDATE works fine, and another with different columns using the same tables, gives me the error Subquery Returns Multiple Rows. Well, it does return multiple rows if I just use the subquery. So does the Update that works. It is my understanding that it HAS to return multiple rows in order to make matches in the where clause and update multiple rows. And anything I have done to try and limit the rows returned in the subquery, gives me another error (Either can’t find the column name, or I ‘can’t update that column’).

This works:
UPDATE j256_corkys_products SET ProductID = (SELECT virtuemart_product_id FROM j256_virtuemart_products WHERE j256_virtuemart_products.product_sku = j256_corkys_products.ItemCode)

This doesn’t
UPDATE j256_virtuemart_products SET product_available_date = (SELECT eta FROM j256_corkys_products WHERE j256_virtuemart_products.virtuemart_product_id = j256_corkys_products.ProductID)

[Schmotty]

name    type    collation   attributes  null    default extra   primary

virtuemart_product_id int(11) UNSIGNED No None AUTO_INCREMENT yes
product_sku char(64) utf8_general_ci Yes NULL
product_available_date datetime No 0000-00-00 00:00:00

Those are the only affected rows in these queries. virtuemart_product_id is the primary key and no others are unique

[Schmotty]

The WHERE is in the subquery like the first one, that works.

and that schema is from the j256_virtuemart_products Table

[Schmotty]

Okay I got it to work.

UPDATE j256_virtuemart_products,
j256_corkys_products SET j256_virtuemart_products.product_available_date = j256_corkys_products.ETA WHERE j256_virtuemart_products.virtuemart_product_id = j256_corkys_products.ProductID

Adding the 2nd table to the update list, but not setting any of the fields, I was able to make the match. Now as to why the other query works just fine, I don’t know.

[Author]

Posts