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…

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.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London



Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron