CTE (common table expression) In Depth

CTE (common table expression) and Updatable CTE you will understand this in this article by sagar jaybhay in depth.

CTE (common table expression)

CTE Introduced in the SQL server 2005.

CTE is like a temporary result set which is defined within the execution of the current context or execution scope of single select, insert, update delete and 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.

with CTEtable
as
(
select d.Department_Name as deptname, COUNT(e.empid) as empcount from Department as d
join Employee as e on d.DepartmentID=e.DepartmentID
group by d.Department_Name
)
select * from CTEtable
where 
empcount>100;

CTE
CTE

In the above query, we didn’t mentioned the column name if your inner query is given distinct column name then there is no need to define column name else you need to define like shown below

with CTEtable(deptname,empcount)
as
(
select d.Department_Name as deptname, COUNT(e.empid) as empcount from Department as d
join Employee as e on d.DepartmentID=e.DepartmentID
group by d.Department_Name
)
select * from CTEtable
where 
empcount>100;
CTE with column name defined
CTE with column name defined

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 returning 3 columns then you need to specify these 3 columns in CTE.

CTE is only referenced by select, insert, update and delete statement immediately follows the CTE expression.

In this, With clause, you can create multiple CTE tables.

with CTEtable(deptname,empcount)
as
(
select d.deptname as deptname, COUNT(e.id) as empcount from tbldept as d
join tblEmp as e on d.deptid=e.deptid
group by d.deptname
),
tblnew_hr(deptname,id)
as
(
select d.deptname,e.id from tblEmp e join tbldept d on
e.deptid=d.deptid
)
select * from CTEtable
union 
select * from tblnew_hr

Multiple CTE
Multiple CTE

Updatable CTE

It is possible to update the CTE the answer to this is Yes or No.

If your CTE is based on a single table then you can update using CTE. Which in turn update the underlying table.

with update_cte
as
(
select id, name, salary from tblEmp
)

update update_cte set salary=5555 where id =2

select * from tblEmp;
Updatable CTE
Updatable CTE

If CTE is based on more than one table and updates affect only the base table then this is possible.

with update_mul_cte
as
(
select e.id,d.deptname,e.geneder from tblEmp e join tbldept d on e.deptid=d.deptid
)

update update_mul_cte set geneder='male' where id=2;
select * from tblEmp;
Multiple CTE Update
Multiple CTE Update

But if you are going to update data in both tables which are present in CTE it will throw an error.

with update_mul_cte
as
(
select e.id,d.deptname,e.geneder from tblEmp e join tbldept d on e.deptid=d.deptid
)

update update_mul_cte set geneder='male',deptname='fff' where id=2;
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.


GitHub Profile :- https://github.com/Sagar-Jaybhay


Sagar Jaybhay, from Maharashtra, India, is currently a Senior Software Developer. He has continuously grown in the roles that he has held in the more than seven years he has been with this company. Sagar Jaybhay is an excellent team member and prides himself on his work contributions to his team and company as a whole.

Related posts