Conditional update either column1 or column2 or column3

Question

I want to write a SQL update statement like

UPDATE p
SET CASE WHEN inv.name='your' THEN p.your_qty=inv.qty
    CASE WHEN inv.name='other' THEN p.other_qty=inv.qty
    CASE WHEN inv.name='my' THEN p.my_qty=inv.qty
FROM products p
JOIN Qty_products_inv inv
ON p.itemNo= inv.itemNo

Answer 1

You need to update all three columns. SET can not update different columns on different rows.

So, pivot your source before joining, then set each column back to its existing value if there is no new value to pick up...

UPDATE
  p
SET
  your_qty  = COALESCE(inv.your_qty , p.your_qty ),
  other_qty = COALESCE(inv.other_qty, p.other_qty),
  my_qty    = COALESCE(inv.my_qty   , p.my_qty   )
FROM
  products p
JOIN
(
  SELECT
    itemNo,
    MAX(CASE WHEN name='your'  THEN qty END) AS your_qty,
    MAX(CASE WHEN name='other' THEN qty END) AS other_qty,
    MAX(CASE WHEN name='my'    THEN qty END) AS my_qty
  FROM
    qty_products_inv
  GROUP BY
    itemNo
)
  inv
    ON p.itemNo = inv.itemNo

Edit

Don't do the following, from my original answer, as @JonArmstrong's comment shows it does not work (unless each row is only ever picking up one single change).

UPDATE
  p
SET
  your_qty  = CASE WHEN inv.name='your'  THEN inv.qty ELSE p.your_qty  END,
  other_qty = CASE WHEN inv.name='other' THEN inv.qty ELSE p.other_qty END,
  my_qty    = CASE WHEN inv.name='my'    THEN inv.qty ELSE p.my_qty    END
FROM
  products p
JOIN
  qty_products_inv inv
    ON p.itemNo = inv.itemNo