crackedcornjimmy crackedcornjimmy - 5 months ago 11
SQL Question

How can I update Order records in SQL based on OrderItem record values

I want to update a flag value in my Orders table, but only if at least one of the OrderItems connected to that Orders record by OrderID, in my OrderItems table, are a certain state.

Scenario 1: All 3 OrderItems connected to an Order are 'Cancelled' - do not update Orders flag

Scenario 2: At least one of the OrderItems connected to an Order are NOT 'Cancelled' - update the Orders flag

Scenario 3: None of the OrderItems connected to an Order are 'Cancelled' - update the Orders flag

How can this be done - and efficiently?

Here is what I have:

UPDATE o
SET o.Flag = true
FROM Orders o
INNER JOIN OrderItems oi ON oi.OrderID = o.OrderID
WHERE o.Flag = false
AND oi.State <> 'Cancelled'


The INNER JOIN will potentially return multiple records from OrderItems. Not sure if this is a problem.

Answer

Basically, required statement should update rows from dbo.Orders table only for scenario 2 and 3.

1) If - for S2 and S3 - you have to update orders with the same flag the one solution is following UPDATE:

UPDATE o
SET Flag = 1 -- common flag for S2 and S3
FROM dbo.Orders o
WHEER EXISTS (
    SELECT * FROM dbo.OrderItems oi
    WHERE oi.OrderID = o.OrderID
    -- Uncomment if oi.Status allows NULLs
    AND (/*oi.Status IS NULL OR*/ oi.Status <> 'Cancelled')
)

2) Buf if you have to update to diff. flags then I would use following statement

UPDATE o
SET Flag = 
    CASE 
        WHEN EXISTS(SELECT * FROM dbo.OrderItems oi2 WHERE oi2.OrderID = o.OrderID AND oi2.Status = 'Cancelled')
        THEN 2 -- Status for second scenario
        ELSE 3 -- Status for third scenario
    END
FROM dbo.Orders o
WHEER EXISTS (
    SELECT * FROM dbo.OrderItems oi
    WHERE oi.OrderID = o.OrderID
    -- Uncomment if oi.Status allows NULLs
    AND (/*oi.Status IS NULL OR*/ oi.Status <> 'Cancelled')
)