Mercer Mercer - 6 months ago 12
SQL Question

Refactoring UPDATE CASE Statements for Performance

It's possible to optimize these queries:

UPDATE PEOPLE
SET ID_STATE = CASE
WHEN ID_STATE = 2 THEN 9
WHEN ID_STATE = 3 THEN 9
WHEN ID_STATE = 7 THEN 8
WHEN ID_STATE = 8 THEN 9
ELSE 0
END
WHERE ID_STATE IN (2,3,7,8)
GO
UPDATE PEOPLE
SET ID_PRESI = CASE
WHEN ID_PRESI = 3 THEN NULL
WHEN ID_PRESI = 4 THEN NULL
ELSE 0
END
WHERE ID_PRESI IN (3,4)
GO
UPDATE STATE_FIC_STATE_PEOPLE
SET ID_STATE = CASE
WHEN ID_STATE = 2 THEN 9
WHEN ID_STATE = 3 THEN 9
WHEN ID_STATE = 7 THEN 8
WHEN ID_STATE = 8 THEN 9
ELSE 0
END
WHERE ID_STATE IN (2,3,7,8)
GO
UPDATE STATE_PEOPLE_PRECIS
SET ID_STATE = CASE
WHEN ID_STATE = 2 THEN 9
WHEN ID_STATE = 3 THEN 9
WHEN ID_STATE = 7 THEN 8
WHEN ID_STATE = 8 THEN 9
ELSE 0
END
WHERE ID_STATE IN (2,3,7,8)
GO
UPDATE STATE_PEOPLE_PRECIS
SET ID_PRESI = CASE
WHEN ID_PRESI = 3 THEN NULL
WHEN ID_PRESI = 4 THEN NULL
ELSE 0
END
WHERE ID_PRESI IN (3,4)
GO

Answer

You've got a bit of redundant code in there;

In this bit, your only options are the 4 values in your where clause, the only one that's different is 7 (returns an 8), everything else returns a 9;

UPDATE PEOPLE  
SET ID_STATE =  CASE  
                        WHEN ID_STATE = 7 THEN 8 
                        ELSE 9
                    END 
WHERE ID_STATE IN (2,3,7,8)
GO

You want anything with a 3 or 4 set to NULL, your where clause will do this. There's no need for the CASE statement;

UPDATE PEOPLE  
SET ID_PRESI =  NULL
WHERE ID_PRESI IN (3,4)
GO

These next two are the same as the first one;

UPDATE STATE_FIC_STATE_PEOPLE  
SET ID_STATE =  CASE  
                        WHEN ID_STATE = 7 THEN 8 
                        ELSE 9
                    END 
WHERE ID_STATE IN (2,3,7,8)
GO

UPDATE STATE_PEOPLE_PRECIS  
SET ID_STATE =  CASE  
                        WHEN ID_STATE = 7 THEN 8 
                        ELSE 9
                    END 
WHERE ID_STATE IN (2,3,7,8)
GO

I've edited slightly from the original answer, the case statements only need to return 8 if ID_STATE = 7, otherwise it's a 9.

Comments