Post
Ask Question

CTE (Common Table Expression) In Depth


CTE was introduced in the 2005 SQL Server. CTE is like a temporary result set which is defined within the execution of the current context or execution scope of a single select, insert, update delete and/or create view statement.
 
It is similar to a derived table and it is not stored as an object like other objects in the SQL server.
 
Remember -- CTE table is created with the keyword.  
  1. with CTEtable  
  2.     as  
  3.     (  
  4.     select d.Department_Name as deptname, COUNT(e.empid) as empcount from Department as d  
  5.     join Employee as e on d.DepartmentID=e.DepartmentID  
  6.     group by d.Department_Name  
  7.     )  
  8.     select * from CTEtable  
  9.     where   
  10.     empcount>100;  

CTE

 
 
In the above query, we didn’t mention the column name. If your inner query is given a distinct column name then there is no need to define the column name, otherwise you need to define it as shown below:
  1. with CTEtable(deptname,empcount)  
  2.     as  
  3.     (  
  4.     select d.Department_Name as deptname, COUNT(e.empid) as empcount from Department as d  
  5.     join Employee as e on d.DepartmentID=e.DepartmentID  
  6.     group by d.Department_Name  
  7.     )  
  8.     select * from CTEtable  
  9.     where   
  10.     empcount>100;  
 
 
In the above query, you specify 2 columns, so remember you need to specify the columns that select query is returning. If our inner select query is returning 3 columns then you need to specify these 3 columns in CTE.
 
CTE is only referenced by select, insert, update and delete statements which immediately follows the CTE expression.
 
In this with clause, you can create multiple CTE tables.  
  1. with CTEtable(deptname,empcount)  
  2.     as  
  3.     (  
  4.     select d.deptname as deptname, COUNT(e.id) as empcount from tbldept as d  
  5.     join tblEmp as e on d.deptid=e.deptid  
  6.     group by d.deptname  
  7.     ),  
  8.     tblnew_hr(deptname,id)  
  9.     as  
  10.     (  
  11.     select d.deptname,e.id from tblEmp e join tbldept d on  
  12.     e.deptid=d.deptid  
  13.     )  
  14.     select * from CTEtable  
  15.     union   
  16.     select * from tblnew_hr   
 

Updatable CTE

 
If your CTE is based on a single table then you can update using CTE, which in turn updates the underlying table. 
  1. with update_cte  
  2.     as  
  3.     (  
  4.     select id, name, salary from tblEmp  
  5.     )  
  6.     update update_cte set salary=5555 where id =2  
  7.     select * from tblEmp;  
 
If CTE is based on more than one table and the updates affect only the base table, then this is possible. 
  1. with update_mul_cte  
  2.     as  
  3.     (  
  4.     select e.id,d.deptname,e.geneder from tblEmp e join tbldept d on e.deptid=d.deptid  
  5.     )  
  6.     update update_mul_cte set geneder='male' where id=2;  
  7.     select * from tblEmp;  
 
But if you are going to update data in both tables which are present in CTE it will throw an error.  
  1. with update_mul_cte  
  2.    as  
  3.    (  
  4.    select e.id,d.deptname,e.geneder from tblEmp e join tbldept d on e.deptid=d.deptid  
  5.    )  
  6.    update update_mul_cte set geneder='male',deptname='fff' where id=2;  
  7.    select * from tblEmp;  

Update Multiple Table CTE Error 

 
Below is an error that is thrown by it.
 
Msg 4405, Level 16, State 1, Line 11
 
View or function ‘update_mul_cte’ is not updatable because the modification affects multiple base tables.