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