Have a look at my sqlfiddle:
http://sqlfiddle.com/#!9/60ffc4 (edited)
Code I am trying to execute:
UPDATE inventory SET
product_name = CONCAT((SELECT name FROM products_and_packaging
WHERE product_id = (SELECT product_id FROM inventory
WHERE inventory_id = 196)), ' 100g Bunch')
WHERE inventory_id = 196
The result I am looking for is for product_name
in the inventory table is renamed from NULL to "Flowers 100g Bunch" where product_id = 196.
I get the #1093 error (You can't specify target table 'inventory' for update in FROM clause)
Note there are answers to similar questions here: You can't specify target table for update in FROM clause
MySQL Error 1093 - Can't specify target table for update in FROM clause
However I tried to apply those solutions and came up with the exact same error due to the Inner Joins that were suggested. Any pointers in the right direction would be great.