如何在sql中从表替换到另一个表期间处理空值？

Question

I have an e-commerce shop and once a week the warehouse puts in another table only products that have changed their price. How to update the prices in the first table with the new prices of the selected products in the other table? Feel free to use also some php if it's not possible to do with mysql only.

I tried this command but when the SELECT founds no matches it changes my original prices to 0 instead of leaving them untouched.

UPDATE product_catalogue pc
SET pc.price = (SELECT new_price
                FROM product_catalogue_updated pcu
                WHERE pc.product_id = pcu.product_id)

Answer 1

This is one possible solution:

UPDATE product_catalogue pc
SET pc.price = (
  SELECT new_price
  FROM product_catalogue_updated pcu
  WHERE pc.product_id = pcu.product_id
)
WHERE pc.product_id IN (
  SELECT pcu.product_id FROM product_catalogue_updated pcu
)

This might work as well:

This doesn't work (but would be nice):

UPDATE product_catalogue pc
SET pc.price = (
  SELECT new_price
  FROM product_catalogue_updated pcu
  WHERE pc.product_id = pcu.product_id
) AS pprice
WHERE pprice IS NOT NULL