[FIXED] How do i update sql value with sum comparison

Issue

I am trying to update a value to show the picked status of an order based on the picked status against the order qty. The data is in the same table but i cannot figure out the correct syntax. I tried: Update Orders set Status = 'FULL' where Sum(Qty_Order) = sum(Qty_Picked) How can i apply this logic using an aggregate query? Thanks in advance for any help/

Solution

One approach uses an update join:

UPDATE Orders o1
INNER JOIN
(
    SELECT id
    FROM Orders
    GROUP BY id
    HAVING SUM(Qty_Order) = SUM(Qty_Picked)
) o2
    ON o2.id = o1.id
SET
    Status = 'FULL';

This assumes that your Orders table has a column id which uniquely identifies each order.

Answered By - Tim Biegeleisen

Answer Checked By - Gilberto Lyons (PHPFixing Admin)