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’).

    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)

    # February 20, 2014 at 10:39 am

    This reply has been reported for inappropriate content.

    Could you provide the table schema?

    # 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:13 pm

    This reply has been reported for inappropriate content.

    What about j256_virtuemart_products which is what you’re updating and have issues with?

    Also, what is the WHERE condition for the update?

    # February 20, 2014 at 12:17 pm

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

    and that schema is from the j256_virtuemart_products Table

    # February 20, 2014 at 12:22 pm

    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.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic.