Complex updates using CTE in SQL Server

Are you familiar with Common Table Expressions (CTE)? Common Table Expressions are widely used to simplify queries, make them more readable and maintainable. For instance, when I want to check how many orders there are for each clients in my database, I can use:

WITH ocnt AS (
    SELECT clientId, count(id) AS cnt
    FROM testOrders
    GROUP BY clientId
)
SELECT c.id, c.clientName, ocnt.cnt
FROM testClients AS c
    LEFT OUTER JOIN ocnt
    ON c.id = ocnt.clientId

The definition of CTE starts with “WITH” and creates a data source for the main query. In the example above, the “ocnt” is simply a query that counts the number of orders per client Id. In the main query, I simply used the “ocnt” data as any other table in the database.

Common Table Expressions can be stacked together, using one another:

WITH ocnt AS (
    SELECT clientId, count(id) AS cnt
    FROM testOrders
    GROUP BY clientId
), clicnt AS (
    SELECT c.id, c.clientName, ocnt.cnt
    FROM testClients AS c
        LEFT OUTER JOIN ocnt
        ON c.id = ocnt.clientId
)
SELECT * FROM clicnt

The above examples are simple, but you should now find use cases in your code. Now, let’s try something less obvious.

Complex updates using CTE

It is not as widely known, but we can also use Common Table Expressions to create update statements that are more complex – for instance – calculating results from one table and place them in another. Let’s say that we want to calculate the average order amount per client and place it into the client’s table. With CTE it is rather easy. First, we create CTE with all the data we need, and then we are updating it as we would do inside the same table. To make things more clear, let’s start with this select-based query:

WITH avgOrd AS (
    SELECT clientId, avg(amountPaid) AS avge
    FROM testOrders
    GROUP BY clientId
)
SELECT c.id, c.average, avgOrd.avge
FROM testClients AS c
    LEFT OUTER JOIN avgOrd
    ON c.id = avgOrd.clientId

As the result, we have the table that contains Client Id, “old” average, and the average calculated from current orders. Now we can encapsulate it in the CTE and perform an update of this “table”:

WITH avgOrd AS (
    SELECT clientId, avg(amountPaid) AS avge
    FROM testOrders
    GROUP BY clientId
), cliAvg AS (
    SELECT c.id, c.average, avgOrd.avge
    FROM testClients AS c
        LEFT OUTER JOIN avgOrd
        ON c.id = avgOrd.clientId
)
UPDATE cliAvg
SET average = avge

Another interesting example is the query that calculates the sort order of elements based on the row number.

WITH rowsOrdered AS (
    SELECT id, sortOrder,
    ROW_NUMBER() OVER (ORDER BY myDesiredOrderColumn ASC) AS desiredOrder
    FROM tableToSort
)
UPDATE rowsOrdered SET sortOrder = desiredOrder

The desired order column can be anything. Date, value, calculated value – whatever you need to set the proper order of elements.

As you can see from the examples above, CTE is a powerful tool. And we don’t even go that far. In practice, you can update multiple tables at the same time, insert values (yes, insert is also possible), and – surprisingly – delete is possible too.