MySQL does not allow updating a table and selecting from the same table at once

#1

Hi! I just ran into a quick issue. Just learned that MySQL does not allow the behaviour of selecting from a table you’re updating. Although my code works perfectly on LocalHost, running it live posed an issue (I wonder why LocalHost didn’t mind? Using Xampp on Windows)

My code:

UPDATE inventory SET 
	product_name = CONCAT((SELECT name FROM products_and_packaging WHERE product_id = (SELECT product_id FROM inventory WHERE inventory_id = 196)), ' 66666g')
WHERE inventory_id = 196

How may I change the exact code to agree with all versions?
The subquery is pretty essential. I tried this:

UPDATE inventory AS a
INNER JOIN inventory AS b ON a.inventory_id=b.inventory_id
SET 
a.product_name = CONCAT((SELECT name FROM products_and_packaging WHERE product_id = (SELECT product_id FROM inventory WHERE b.inventory_id = 196)), ' 66666g')
WHERE a.inventory_id = 196

But it didn’t work. Gave me an error #1093 - You can't specify target table 'a' for update in FROM clause
Same error all over again!

Found some answers on StackOverflow but I can’t seem to apply any without the same error recurring.

#2

Embedded SELECT’s seldom work! Also, if you are CONCAT’ing strings, you might need CONCAT_WS to make that part work.

Is there a reason you don’t use two queries? OR, like most people do, use a JOIN or UNION to link the three tables together. Using nested SELECT’s are tricky.

To debug this, run the most inner select by itself and see what it gets for results. Then, use the next most inner one using that data and track back to see where the error is. But, I would go with a simple join… Much better database system wise!

To do that, you join the second and third items and just use CONCAT_WS to put results together like in your first example. You want it like this, I think…

update inventory A SET product_name = B.name WHERE inventory_id = 196
JOIN products_and_packaging B ON A.inventory_id = B.product_id

Not sure exactly as you did not show us how both the tables are set up… Good luck!

1 Like
#4

Hi Ernie! I’ve built a little sql fiddle to help clarify my question:

I tried your solution but another error came up, please have a look! :slight_smile:

#5

I’ve made a bit of a jump here - your original query mentions an inventory ID of 196 but your fiddle only shows a product ID with that value. Should you be referring to product ID instead of inventory ID? If so, you can rewrite your query as follows:

UPDATE inventory i
JOIN products_and_packaging pap ON i.product_id = pap.product_id
SET i.product_name = CONCAT(pap.name, ' 66666g')
WHERE i.product_id = 196;

You can see what’s going on here if you run the below query:

SELECT * FROM inventory i
JOIN products_and_packaging pap ON i.product_id = pap.product_id;

This will show you a list of inventory rows and their corresponding products_and_packaging rows. All MySQL then needs to do is pick the correct row, and make the required change from the information it has there.

1 Like
#6

Hi! :slight_smile:

I just updated the fiddle to include the product_id. I had indeed referred to a product_id. Apologies for the error:

Just checked your solution, I changed the last line from product_id to inventory_id and hey presto it works!!! SQL Fiddle

You’re a star!

Sponsor our Newsletter | Privacy Policy | Terms of Service