lrey lrey - 4 months ago 9
SQL Question

Conditional UPDATE statement with JOIN

I'm trying to update a column

Plan_Status
based on the value of another table
main
, column
Days_At_Step
.
The following statements work, however, how would I structure this as a conditional statement?
Or, is there another strategy that I could use?

I feel like there's a better way than using three identical statements with different WHERE conditions.

UPDATE controlState
SET Plan_Status=1
FROM controlState JOIN main ON main.Cscc = controlState.Cscc
WHERE Days_At_Step < 2;

UPDATE controlState
SET Plan_Status=2
FROM controlState JOIN main ON main.Cscc = controlState.Cscc
WHERE (Days_At_Step >= 2 OR Days_At_Step < 4);

UPDATE controlState
SET Plan_Status=3
FROM controlState JOIN main ON main.Cscc = controlState.Cscc
WHERE Days_At_Step > 4;

Answer

The three where clauses cover all the possible values Days_At_Step could get, so you really don't need a where clause at all. Instead, you can move this logic to a case expression:

UPDATE controlState 
SET    Plan_Status = CASE WHEN Days_At_Step < 2 THEN 1 
                          WHEN (Days_At_Step >= 2 OR Days_At_Step < 4) THEN 2
                          ELSE 3
                     END
FROM   controlState 
JOIN   main ON main.Cscc = controlState.Cscc;