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
go

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

select *
into temp
from
sales.SalesOrderHeader
go

-- add an empty column to hold our data
alter table temp add totalOrderQty smallint
go

create unique clustered index ix_tempSalesOrderHeader on temp(salesOrderID)
go

-- CTE obtaining the totalOrderQtyNumber from sales.salesOrderDetail
with maxOrder as
(
  
select t.SalesOrderID, totalOrderQty, d.totalOrderQtyNumber
  
from temp t
  
join
  
(
      
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
go

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

-- cleanup
drop table temp
go


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