Error code 1052 on Updating two tables incorporating a subquery

Question

I am having issues updating two tables using a sub-query in the same query. This is part of a course project and it has to include both of those statements. I understand how sub-queries work and I am able to update the two tables at once, however it is still giving me this error when I try to incorporate the sub-query.

I have looked online and the only fix that I can find is to assign the fields an alias to avoid the ambiguous error. However I am not sure how to assign it in the update statement.

update item, shipment 
set item.price = 6.99,
    shipment.price = 6.99
where price like(
              select price
              from item
              join shipment on item.ship_id = shipment.ship_id
              where item_num = "android543");

I understand that this is ambiguous I am just not sure how to assign the values an alias.

Sample data, desired results, and an explanation of what you want to accomplish would all help. — Gordon Linoff, Aug 24 '19 at 23:04
`LIKE` is for character comparisons, not numbers. Numeric comparisons use numeric operators such as `=`, `<`, `>`, `<>`,`<=` and `>=`. `WHERE LastName LIKE 'Smi%'` is valid; `WHERE price LIKE 123` is not. — Ken White, Aug 24 '19 at 23:06
@NeerajAgarwal sure you can. https://stackoverflow.com/questions/4361774/mysql-update-multiple-tables-with-one-query — Nick, Aug 25 '19 at 04:51
@Nick My bad I did not pay attention to the fact that this was for MySQL. — Neeraj Agarwal, Aug 25 '19 at 05:44

Nick · Answer 1 · 2019-08-24T23:42:02.793

You have two variables called price (item.price and shipment.price) and MySQL doesn't know which one you are referring to in:

where price like(

You need to be specific about which one you want e.g.

where item.price like(

Note that like, although it might work in this situation (e.g. 4.5 like 4.5 is true, but 4.5 like 4.50 is not), is intended for character type variables. A numeric operator such as = or possibly IN (if you expect multiple results from the subquery) is more appropriate in this case.

If I have understood your question correctly, you can probably more simply rewrite your query like this:

update item i
join shipment s on i.ship_id = s.ship_id
set i.price = 6.99,
    s.price = 6.99
where i.item_num = "android543"

Error code 1052 on Updating two tables incorporating a subquery

1 Answers1