Today I have received a fantastic email from Matthew Spieth. SQL Server expert from Ohio. He recently had a great conversation with his colleagues in the office and wanted to make sure that everybody who reads this blog knows about this little feature which is commonly confused.
Here is his statement and we will start our story with Matthew’s own statement: “Users often confuse CTE with Temp Table but technically they both are different, CTE are like Views and they can be updated just like views.“
Very true statement from Matthew. I totally agree with what he is saying. Just like him, I have enough, time came across a situation when developers think CTE is like temp table. When you update temp table, it remains in the scope of the temp table and it does not propagate it to the table based on which temp table is built. However, this is not the case when it is about CTE, when you update CTE, it updates underlying table just like view does.
Here is the working example of the same built by Matthew to illustrate this behavior.
Check the value in the base table first.
USE AdventureWorks2012; -- Check - The value in the base table is updated SELECT Color FROM [Production].[Product] WHERE ProductNumber = 'CA-6738';
Now let us build CTE with the same data.
USE AdventureWorks2012; ;WITH CTEUpd(ProductID, Name, ProductNumber, Color) AS( SELECT ProductID, Name, ProductNumber, Color FROM [Production].[Product] WHERE ProductNumber = 'CA-6738')
Now let us update CTE with following code.
-- Update CTE UPDATE CTEUpd SET Color = 'Rainbow';
Now let us check the BASE table based on which the CTE was built.
-- Check - The value in the base table is updated SELECT Color FROM [Production].[Product] WHERE ProductNumber = 'CA-6738';
That’s it! You can update CTE and it will update the base table.
Here is the script which you should execute all together.
USE AdventureWorks2012; -- Check - The value in the base table is updated SELECT Color FROM [Production].[Product] WHERE ProductNumber = 'CA-6738'; -- Build CTE ;WITH CTEUpd(ProductID, Name, ProductNumber, Color) AS( SELECT ProductID, Name, ProductNumber, Color FROM [Production].[Product] WHERE ProductNumber = 'CA-6738') -- Update CTE UPDATE CTEUpd SET Color = 'Rainbow'; -- Check - The value in the base table is updated SELECT Color FROM [Production].[Product] WHERE ProductNumber = 'CA-6738';
If you are aware of such scenario, do let me know and I will post this on my blog with due credit to you.
Reference: Pinal Dave https://blog.sqlauthority.com/ )
16 Comments. Leave new
Delete also works with CTEs
Nice observation.
Hello Pinal ,
Few years back u have written one blog regarding Delete duplicate records from Table by taking the help of CTE.
This is add on that delete blog..
One thing we have noticed with using CTE’s to do updates is that the base table (from with the Cte was built) is locked to any operation it even appears select with nolock does not work.
Great article, I learned new thing, thanks Matthew’s and Pinal.
Ya nice article Pinal ,Updating CTE is a good article, but i have a one doubt. In which scenario it is usable , where we have scenario like update CTE, normally CTE is used for select a Set of records with temp execution of next line
Good Observation that this is like a View. It’s much more comfortable to think of it as a View that lives only for the life of the query and provides for convenience and optimization of the query, just like a View does on a more permanent basis.
Always Pinal dave articles are Excellent …… I do prefer always his article ….
Thanks,
Sutha.k
I have one small doubt, we can directory update base table by following query
Update [Production].[Product] SET Color=’RAINBOW’ where ProductNumber=’CA-6738′
Then why do we need to create CTE, if there is any special things can be done by CTE?
I get an error when the CTE query contains an aggregation function. (Example: Join customers to orders, find the first order date for the customer from order table and update a field in customers to contain it. The field to be updated is in the GROUP BY.) I was shown that you can put the aggregation a subquery, which seems silly to me but it works.
Hi Pinal Dave,
I have query.
When we create CTE with GROUP BY clause, we cannot update the CTE.
Can you please give me any work aroung that i can update the CTE.
very good and useful article, thank you
Great article, I’ve been using CTEs quite a bit for reporting but just recently discovered this feature. One shortcoming that I have not yet been able to get around is that apparently you cannot update a recursive CTE. Whenever I try to do it I get the error:
[Error Code: 4421, SQL State: S0001] Derived table ‘MyCTE’ is not updatable because a column of the derived table is derived or constant.
Getting ‘Derived table ‘cte_Exp1′ is not updatable because a column of the derived table is derived or constant.’
error, how to sort out the below query?
i just want to display the numbers as below format
———————–
A num label
———————–
1 Odd
2 Even
3 Odd
4 Even
……
with [cte_Exp1] as(
select 1 as [A num],
‘Num’ as [label]
union all
select [A num]+1,
[label]
from [cte_Exp1]
where [A num]<9
)
–select * from [cte_Exp1]
UPDATE [cte_Exp1]
SET [label]=case when [A num]%2=1 then 'Odd'
when [A num]%2=0 then 'even'
else 'null'
end
select * from [cte_Exp1]
Good Article, I have a question related to this- Can CTE update the base table itself when CTE have a row_number over partition, looks like ‘no, it cant’.
I have a scenario where I am finding duplicate rows using partition and then update base table all rows when row_number returned row_number > 1. Basically I am trying to mark them rejected when I have duplicate.
I am getting below error –
Update or insert of view or function ‘xxxx failed because it contains a derived or constant field.