Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


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.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

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

Categories


© Copyright 2020 SQLTraining Ltd.