MySQL Update Troubles
# February 20, 2014 at 10:25 am
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’).
UPDATE j256_corkys_products SET ProductID = (SELECT virtuemart_product_id FROM j256_virtuemart_products WHERE j256_virtuemart_products.product_sku = j256_corkys_products.ItemCode)
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)# February 20, 2014 at 11:55 am
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# February 20, 2014 at 12:22 pm
Okay I got it to work.
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.
You must be logged in to reply to this topic.