In SQL Server, Common Table Expressions (CTEs) are often used for readability and simplifying complex queries. While CTEs are most commonly used when running a SELECT query, we can also use CTEs to perform updates with the UPDATE statement. This can be useful when we need to reference the same set of data multiple times or want to update records conditionally.

The Process

The process generally involves:

1. Defining the CTE to identify or calculate the rows needing updates.
2. Using the CTE results as the target for the UPDATE statement.

Example

Imagine we have a Customers table and a BankAccounts table. Each customer can have multiple bank accounts, which are listed as separate rows in the BankAccounts table. And the Customers table has a column that indicates whether the customer is “rich” or “poor” based on the total amounts in their bank accounts.

The following example uses this scenario, and we can use the CTE along with the UPDATE statement to update the Customers table for each customer based on their total balance in the BankAccounts table.

Create Table and Populate with Initial Data

First, let’s create the tables for the customers and their bank balances. We’ll also populate the tables with initial data.

-- Create Customers table
CREATE TABLE Customers (
    CustomerID INT IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(100),
    LastName NVARCHAR(100),
    Status NVARCHAR(10) -- This will hold 'Poor' or 'Rich'
);

-- Create BankAccounts table
CREATE TABLE BankAccounts (
    AccountID INT IDENTITY(1,1) PRIMARY KEY,
    CustomerID INT,
    Balance DECIMAL(10, 2),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

-- Insert initial data into Customers
INSERT INTO Customers (FirstName, LastName, Status)
VALUES 
('Aarav', 'Patel', NULL),
('Maria', 'Garcia', NULL),
('Li', 'Wei', NULL),
('Fatima', 'Al-Farsi', NULL),
('James', 'Smith', NULL);

-- Insert initial data into BankAccounts
INSERT INTO BankAccounts (CustomerID, Balance)
VALUES
(1, 500.00),   -- Aarav Patel
(1, 1200.00),  -- Aarav Patel
(2, 300.00),   -- Maria Garcia
(3, 4000.00),  -- Li Wei
(4, 50.00),    -- Fatima Al-Farsi
(4, 30.00),    -- Fatima Al-Farsi
(5, 2000.00),  -- James Smith
(5, 3500.00);  -- James Smith

-- Select data from the Customers table
SELECT * FROM Customers;

Output:

CustomerID  FirstName  LastName  Status
----------  ---------  --------  ------
1           Aarav      Patel     null  
2           Maria      Garcia    null  
3           Li         Wei       null  
4           Fatima     Al-Farsi  null  
5           James      Smith     null  

We can see that the Status column is currently full of NULL values. Our CTE will update with values, based on each customer’s bank balance.

Use a CTE to Update the Table

Let’s go ahead and create the CTE, then use an UPDATE statement to update the table based on the result of the CTE:

-- Use CTE to calculate total balance for each customer
WITH CustomerBalances AS (
    SELECT 
        C.CustomerID,
        SUM(BA.Balance) AS TotalBalance
    FROM 
        Customers C
    INNER JOIN 
        BankAccounts BA ON C.CustomerID = BA.CustomerID
    GROUP BY 
        C.CustomerID
)

-- Update Customers table based on total balance
UPDATE C
SET C.Status = CASE 
                  WHEN CB.TotalBalance >= 3000 THEN 'Rich'
                  ELSE 'Poor'
               END
FROM 
    Customers C
INNER JOIN 
    CustomerBalances CB ON C.CustomerID = CB.CustomerID;

-- Verify the updated Customers table
SELECT * FROM Customers;

Result:

CustomerID  FirstName  LastName  Status
----------  ---------  --------  ------
1           Aarav      Patel     Poor  
2           Maria      Garcia    Poor  
3           Li         Wei       Rich  
4           Fatima     Al-Farsi  Poor  
5           James      Smith     Rich  

Now the Status column has been updated based on the amount of money in their account.

Here’s an explanation of the code:

• CTE: The Common Table Expression (CustomerBalances) calculates the total balance for each customer by summing up the balances from the BankAccounts table.
• Update: The UPDATE statement uses the CTE to update the Status column in the Customers table. If a customer’s total balance is 3000 or more, they are marked as Rich; otherwise, they are marked as Poor.
• Validation: A SELECT statement is included at the end to display the updated Customers table.