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.
- 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
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:
- 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;
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.
- 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
Updatable CTE
If your CTE is based on a single table then you can update using CTE, which in turn updates 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;
If CTE is based on more than one table and the 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;
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.