MySQL UPDATE if exist, INSERT if not into multiple tables

Viewed 30

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

0 Answers
Related