CTEs are Updateable
Published: 26th October 2014
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…

use AdventureWorks2012

if object_id('temp') is not null drop table temp

select *
into 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
from sales.SalesOrderDetail
group by SalesOrderID
) d
on t.SalesOrderID = d.SalesOrderID
-- update the CTE directly which updates our new column in temp
update maxOrder
set totalOrderQty = totalOrderQtyNumber

-- the column has now been updated...
select SalesOrderID, totalOrderQty
from temp

-- cleanup
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.
NB: Comments will only appear once they have been moderated.