sql - 重构 UPDATE CASE 语句以提高性能

Question

是否可以在性能方面优化这些查询:

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 1

我会和 Rich Benner 一样，因为你这里有很多冗余

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

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

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

GO