How Can I Efficiently Update Records with SQL Server 2005's CASE Statement?

How to Efficiently Update Records Using CASE in SQL Server 2005

In SQL Server 2005, the CASE statement offers a versatile way to perform conditional updates. However, when dealing with tables with a large number of records, it's crucial to use it judiciously to avoid performance bottlenecks.

Consider the following query:

UPDATE dbo.TestStudents
SET LASTNAME =
( CASE
    WHEN (LASTNAME = 'AAA') THEN 'BBB'
    WHEN (LASTNAME = 'CCC') THEN 'DDD'
    WHEN (LASTNAME = 'EEE') THEN 'FFF'
    ELSE (LASTNAME)
END )

This query successfully updates the specified records based on the given conditions. However, it uses an ELSE condition that scans through every row in the table, even those that don't need updating. This can significantly impact performance in large tables.

To optimize the query, you can add a WHERE clause to filter the rows that should be updated:

UPDATE dbo.TestStudents
SET     LASTNAME =  CASE
                        WHEN LASTNAME = 'AAA' THEN 'BBB'
                        WHEN LASTNAME = 'CCC' THEN 'DDD'
                        WHEN LASTNAME = 'EEE' THEN 'FFF'
                        ELSE LASTNAME
                    END
WHERE   LASTNAME IN ('AAA', 'CCC', 'EEE')

By specifying the specific values that need to be updated in the WHERE clause, the query only scans the rows that match those criteria. This significantly improves performance and ensures that unaffected rows remain unchanged.

The above is the detailed content of How Can I Efficiently Update Records with SQL Server 2005's CASE Statement?. For more information, please follow other related articles on the PHP Chinese website!