What happens when you update Common Table Expressions (CTEs)?

Advance your SQL skills in CTEs

Photo by Elena Taranenko on Unsplash

Table of Contents

In this article, we will see 3 different update scenarios in CTEs such as :

1. CTE based on a single base table
2. CTE based on more than 1 base table and update affects 1 base table
3. CTE based on more than 1 base table and update affects 2 base tables

Consider the following 2 tables Employee and Department

Employee

Department

CTE based on single base table

Lets create a CTE based on tblEmployee and update gender for id=20

With EmpUpdateGender
as
( Select Id, Name, Gender from tblEmployee)
Update EmpUpdateGender Set Gender = 'Female' where Id = 20

Update Succeeds

Updated Employee Table: Alen’s gender is updated

CTE based on more than 2 base tables and update affects 1 base table

Let’s create a CTE based on tblEmployee and tblDepartment and update gender for id=20

With EmpUpdateGender as 
(Select Id, Name, Gender, DeptName
from tblEmployee
join tblDepartment
on tblDepartment.DeptId = tblEmployee.DepartmentId)
Update EmpUpdateGender set Gender = 'Male' where Id = 20

Update Succeeds

Update Employee Table: Alen’s gender is updated

CTE based on 2 base tables and update on 2 base tables

With EmpByDeptName as
(Select Id, Name, Gender, DeptName 
from tblEmployee 
join tblDepartment 
on tblDepartment.DeptId = tblEmployee.DepartmentId)
Update EmpByDeptName set Gender = 'Female', DeptName='Engineering' where id = 20

No Update!!!!!!!!!!!!!!!!!!

ERROR !!!!!!

In short, if a CTE is based on more than 1 base table and update affects more than 1 base table, then update is not allowed(throws an error)

We will see one final case to a particular scenario where we have to be really very careful while updating CTE

With EmpByDeptName as
(Select Id, Name, Gender, DeptName 
from tblEmployee 
join tblDepartment 
on tblDepartment.DeptId = tblEmployee.DepartmentId)
Update EmpByDeptName set DeptName='Production' where id = 30

Update Succeeds. Be Careful !!!!!!!!!!!

Data Comparison when DeptName is updated for Id=30

After you execute the UPDATE, and when you try to select data from the CTE, and you will see that Alex’s DeptName is also changed to Production.

So here, the update does not work as expected as it is changing Alex’s DeptName too. This is because, the update has changed the DeptName from Engineering to Production.

Conclusion

We have seen 3 different cases of update in CTE :
1) CTE based on 1 base table and update succeeds as expected

2) CTE based on 2 base tables and update on 2 base tables, query terminates with error

3 ) CTE based on 2 base tables and update on 1 base table, update succeeds. However, update is not working as expected