Colesce sum inner join

Question

Goodmorning, I read a lot about this problem in the group, but I really don't understand why my code doesn't work. If I add products in the total everything is ok; if I delete some product the total is still correct, but if no product is present the total doesn't go to 0 or null but it stays the same as the last calculation.

I thought that with Coalesce my problem was solved but it doesn't work anyway.

This is my phpmyadmin code:

UPDATE orders o 
                INNER JOIN
                (
                   SELECT order_id, COALESCE(SUM(totale),0) 'sumu'
                   FROM OrdineProdotto 
                   GROUP BY order_id
                ) i ON o.order_id = i.order_id
                SET o.totale_ordine = i.sumu
                WHERE o.order_id = $ordID

where $ordID is obviously the number of the order.

Thank you so much for the help.

Answer 1

If the subquery does not contain a row for a specific order_id then the row in orders with that order_id will not be updated because of the join.
Instead use a correlated subquery:

UPDATE orders o 
SET o.totale_ordine = (
  SELECT COALESCE(SUM(op.totale), 0) 
  FROM OrdineProdotto op 
  WHERE op.order_id = o.order_id
)
WHERE o.order_id = $ordID;