There's two related tables. t_goods(id, name, descriprion) and t_prices(goods_id, price). I need to set/update the field 'description' from t_goods and the field 'price' from t_prices. Here's my solution:
UPDATE `t_goods`
LEFT JOIN
`t_prices`
ON t_prices.product_id = t_goods.id
INNER JOIN
(SELECT 'bla-bla-bla' AS description, '123456' AS id, 1000 AS price) AS DataToUpdateTable
ON DataToUpdateTable.id= t_goods.id
SET t_prices.price = DataToUpdateTable.price,
t_goods.description = DataToUpdateTable.description
That works fine, but there's a problem: if no price is set before (t_prices.price is NULL in the above query) it remains unset. I need to INSERT instead of UPDATE in that case. And MySQL offers a solution (INSERT ... ON DUPLICATE KEY UPDATE Statement). But it's impossible to INSERT into multiple tables. How can I workaround that problem? I kindly ask for your advice