CTEs are Updateable
Published: Jun 03, 2016
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…
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.
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