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
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.
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

Categories


Archives


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