CTE with Multiple Update Statements

Introduction

In this post, we'll explore how to use Common Table Expressions (CTE) with multiple update statements in Microsoft SQL Server. CTEs are a powerful and flexible feature that allows you to create temporary result sets, making it easier to write complex and maintainable queries.

Understanding CTEs

A CTE is a named temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs are particularly useful when working with recursive queries or breaking down complex queries into smaller, more manageable parts.

Combining CTEs with Update Statements

CTEs can be used alongside UPDATE statements to modify data in your tables based on the result set generated by the CTE.

Here's an example to illustrate how to use a CTE with multiple update statements:

-- Sample data
CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    Name NVARCHAR(50),
    Department NVARCHAR(50),
    Salary INT
);

INSERT INTO Employee VALUES (1, 'John Doe', 'HR', 50000);
INSERT INTO Employee VALUES (2, 'Jane Smith', 'IT', 55000);
INSERT INTO Employee VALUES (3, 'Mary Johnson', 'IT', 60000);

-- CTE with multiple update statements
WITH UpdateCTE AS (
    SELECT 
        EmployeeID,
        Salary,
        Department,
        CASE
            WHEN Department = 'HR' THEN Salary * 1.10
            ELSE Salary * 1.05
        END AS NewSalary
    FROM Employee
)
UPDATE Employee
SET Salary = UpdateCTE.NewSalary
FROM Employee
JOIN UpdateCTE ON Employee.EmployeeID = UpdateCTE.EmployeeID;

In this example, we first create a sample table named Employee and insert some data into it. Next, we define a CTE called UpdateCTE, which calculates the new salary for each employee based on their department. Finally, we use the UPDATE statement to update the salaries in the Employee table using the result set from the UpdateCTE.

Conclusion

Common Table Expressions provide a powerful way to simplify complex SQL queries and improve code readability. By combining CTEs with multiple update statements, you can efficiently manage and update data in your Microsoft SQL Server tables.