How to Update Records in Table from CTE in SQL

Common Table Expressions (CTEs) in SQL is an important feature that provides a temporary result set that can be referred to within a SELECT, INSERT, UPDATE, or DELETE statement.

In this article, we will learn how to use CTEs to update records in SQL along with examples along with an explanation.

What is Common Table Expression (CTE )?

A CTE (Common Table Expression) in SQL is defined as a temporary result set or a "virtual table" that we can refer to within a SELECT, INSERT, UPDATE, or DELETE query. It is defined using the WITH keyword and helps to simplify complex queries by breaking them into smaller, more manageable parts.

Syntax:

WITH CTE_Name AS (
    SELECT column1, column2, ...
    FROM TableName
    WHERE condition
)
UPDATE TableName
SET column = new_value
FROM TableName
JOIN CTE_Name
ON TableName.id = CTE_Name.id;

Example of Updating Records with a CTE

To understand how to update records with the CTE in SQL, We will consider one table called Employees to execute queries are shown below

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100),
    Department VARCHAR(50),
    Salary DECIMAL(10, 2)
);

INSERT INTO Employees (EmployeeID, Name, Department, Salary) VALUES
(1, 'Alice', 'HR', 50000),
(2, 'Bob', 'IT', 60000),
(3, 'Charlie', 'IT', 70000),
(4, 'David', 'HR', 55000);

Employees Table:

Example 1: Increasing Salaries of IT Department Employees Using CTE

Suppose we want to increase the salary of IT department employees by 10%. The query using a CTE and UPDATE statement is shown below:

Query:

WITH IT_Employees AS (
    SELECT EmployeeID, Salary
    FROM Employees
    WHERE Department = 'IT'
)
UPDATE e
SET e.Salary = e.Salary * 1.10
FROM Employees e
JOIN IT_Employees it
ON e.EmployeeID = it.EmployeeID;

Output:

Explanation:

• This query first creates a temporary table IT_Employees that retrieves the EmployeeID and Salary of employees from the 'Employees' table who belong to the 'IT' department.
  
• Then, the UPDATE statement joins the original 'Employees' table with the IT_Employees table on the EmployeeID.
  
• For the matched records it updates the Salary by multiplying it by 1.10 and giving a 10% raise to all IT department employees.

Example 2: Updating Employee Department Based on Salary Using CTE

Let's find employees with salaries greater than $60,000 and update their department to 'Executive' in the Employees table.

Query:

WITH HighEarners AS (
    SELECT EmployeeID
    FROM Employees
    WHERE Salary > 60000
)
UPDATE Employees
SET Department = 'Executive'
FROM Employees
JOIN HighEarners
ON Employees.EmployeeID = HighEarners.EmployeeID;

Output:

Explanation:

• The WITH clause defines a CTE named HighEarners which selects the EmployeeID of employees earning more than $60,000.
• The UPDATE statement modifies the Department field of employees in the Employees table.
• A JOIN matches records from the Employees table with the HighEarners CTE to ensure only the relevant rows are updated.

Benefits of Using CTEs for Updates

• Improved Readability: CTEs make the query easier to read and maintain.
• Modular Design: Complex logic can be broken into smaller parts.
• Reusable Logic: The same CTE can be used in multiple operations within the same query.
• Focus on Target Rows: By isolating rows in the CTE, the UPDATE operation becomes more precise.

Conclusion

Overall, Using CTEs to update records in SQL is a powerful technique that enhances code clarity and maintainability. Whether you're applying complex update conditions or simply modifying a subset of rows, CTEs provide a structured and efficient approach. After reading whole article now you have clear understanding about how to use CTEs to update record in table very easily.