TSQL: Streamline your Data Updates with CTE

Common Table Expressions (CTEs) are a powerful feature in SQL Server that allow you to define temporary result sets within a query. They are often used to simplify complex queries, improve query readability, and enable the reuse of intermediate results. CTEs can also be used together with UPDATE statements. We will walk through an example in this post.

Sample Data

You can use this Fiddle to follow along and practice CTE-based UPDATE

Create Employee and Department tables with sample data
-- Create Employee table
CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    DepartmentID INT,
    Salary DECIMAL(10, 2)
);

-- Insert sample data into Employee table
INSERT INTO Employee (EmployeeID, FirstName, LastName, DepartmentID, Salary)
VALUES
    (1, 'John', 'Doe', 1, 50000.00),
    (2, 'Jane', 'Smith', 2, 60000.00),
    (3, 'Michael', 'Johnson', 1, 55000.00),
    (4, 'Emily', 'Williams', 3, 70000.00),
    (5, 'David', 'Brown', 2, 62000.00);

-- Create Department table
CREATE TABLE Department (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(50)
);

-- Insert sample data into Department table
INSERT INTO Department (DepartmentID, DepartmentName)
VALUES
    (1, 'Sales'),
    (2, 'Marketing'),
    (3, 'Engineering'),
    (4, 'Human Resources');

SELECT *
FROM Employee;

SELECT *
FROM Department;

Employee Table:

EmployeeID	FirstName	LastName	DepartmentID	Salary
1	John	Doe	1	50000.00
2	Jane	Smith	2	60000.00
3	Michael	Johnson	1	55000.00
4	Emily	Williams	3	70000.00
5	David	Brown	2	62000.00

Department Table:

DepartmentID	DepartmentName
1	Sales
2	Marketing
3	Engineering
4	Human Resources

Using CTEs with UPDATE:

Let's consider a scenario where you want to update the salaries of employees in the Employees table based on certain criteria.

Perform CTE-based UPDATE
WITH EngineeringSalaryUpdate
AS (SELECT EmployeeID,
           Salary AS OldSalary,
           Salary * 1.1 AS NewSalary
    FROM Employee AS E
        INNER JOIN Department AS D
            ON E.DepartmentID = D.DepartmentID
    WHERE D.DepartmentName = 'Engineering')
UPDATE EngineeringSalaryUpdate
SET OldSalary = NewSalary

This above SQL UPDATE query utilizes a Common Table Expression (CTE) to perform an update on employee salaries within the 'Engineering' department. Let's break down the steps:

CTE Definition: The query begins by defining a CTE named "EngineeringSalaryUpdate". This CTE is constructed by selecting specific columns from two tables, "Employees" and "Department", and performing a JOIN operation based on the common "DepartmentID" field. The selected columns include "EmployeeID", the existing "Salary" (referred to as "OldSalary"), and a new calculated salary ("Salary * 1.1", which is a 10% increase, as "NewSalary"). The selection is filtered to include only employees within the 'Engineering' department.
Update Operation: After the CTE is defined, the query proceeds to an UPDATE operation. The target of the update is the "EngineeringSalaryUpdate" CTE. This means the subsequent update will affect the rows generated by the CTE's SELECT statement.
Column Update: Within the UPDATE operation, the query sets the "OldSalary" column in the "EngineeringSalaryUpdate" CTE to be equal to the "NewSalary" value. This essentially updates the "OldSalary" value with the 10% increased salary calculated earlier in the CTE.

In essence, this query updates the salary of employees (Emily Williams in our example) in the 'Engineering' department by increasing their salaries by 10%, all done using a CTE to simplify the process. The UPDATE statement targets the rows generated by the CTE's SELECT statement and updates the "OldSalary" value with the calculated "NewSalary" value.

List all employees and check for salary increase of employees in the engineering department
SELECT *
FROM Employee;

EmployeeID	FirstName	LastName	DepartmentID	Salary
1	John	Doe	1	50000.00
2	Jane	Smith	2	60000.00
3	Michael	Johnson	1	55000.00
4	Emily	Williams	3	77000.00
5	David	Brown	2	62000.00

Sample Data​

Sample Data