Now to all those people saying “well, duh”, this clearly isn’t a post for you, but this is genuinely something I didn’t realize until I saw it the other day and went to try it out for myself.
To be honest it makes perfect logical sense that it works because a CTE is effectively just a nicely formatted subquery or derived table, but I’d never even thought to use it this way.
To be honest, even if you’ve not seen this done before, I don’t think it warrants any actual explanation, therefore this post is going to be almost purely code from now on.
Here’s my example, I’m adding a new column to my copy of SalesOrderHeader (called “temp”) which will hold the Total Order Quantity taken from the SalesOrderDetail table…
if object_id('temp') is not null drop table temp
-- add an empty column to hold our data
alter table temp add totalOrderQty smallint
create unique clustered index ix_tempSalesOrderHeader on temp(salesOrderID)
-- CTE obtaining the totalOrderQtyNumber from sales.salesOrderDetail
with maxOrder as
select t.SalesOrderID, totalOrderQty, d.totalOrderQtyNumber
from temp t
select salesOrderID, sum(orderQty) totalOrderQtyNumber
group by SalesOrderID
on t.SalesOrderID = d.SalesOrderID
-- update the CTE directly which updates our new column in temp
set totalOrderQty = totalOrderQtyNumber
-- the column has now been updated...
select SalesOrderID, totalOrderQty
drop table temp
As you can see, it’s actually a very powerful method and simple to write and understand. Potentially even a neater method than doing this without the CTE. But that’s down to personal preference.