SQL SERVER Topic

Updatable CTE in SQL
Question:- It is possible to update CTE?

ANSWER:- Yes & No depending on the number of base tables, the CTE is created upon, and the number of base tables affected by the UPDATE statement.
  1. A CTE is based on a single base table, then the UPDATE suceeds and works as expected.
  2. A CTE is based on more than one base table, and if the UPDATE affects multiple base tables, the update is not allowed and the statement terminates with an error.
  3. A CTE is based on more than one base table, and if the UPDATE affects only one base table, the UPDATE succeeds(but not as expected always)
Now, we are going to explain by best way example as following.

We are using SQL Server. So first of all we create two tables named are tblStudent and tblBranch and insert some records as shown in below. 

  

-----------tblStudent--------------

CREATE TABLE tblStudent
(
  Id int Primary Key,
  Name nvarchar(30),
  Email nvarchar(200),
  Gender nvarchar(10),
  BranchId int
)


-----------tblBranch--------------

CREATE TABLE tblBranch
(
 BranchId int Primary Key,
 BranchName nvarchar(20)
)


----------- Insert records into tblStudent--------------

INSERT INTO tblStudent VALUES(1001,'Santosh','s@gmail.com','Male',1)
INSERT INTO tblStudent VALUES(1002,'Pooja kumari','p@gmail.com','Female',2)
INSERT INTO tblStudent VALUES(1003,'Arun kuamr','a1@gmail.com','Male',1)
INSERT INTO tblStudent VALUES(1004,'Pragya','p@gmail.com','Female',3)
INSERT INTO tblStudent VALUES(1005,'Manorma','m@gmail.com','Female',4)
INSERT INTO tblStudent VALUES(1006,'Ojha','o@gmail.com','Male',4)
INSERT INTO tblStudent VALUES(1007,'Kavita','k@gmail.com','Female',2)
INSERT INTO tblStudent VALUES(1008,'Dr. Prem kumar','pk@gmail.com','Male',4)
INSERT INTO tblStudent VALUES(1009,'Arti','a@gmail.com','Female',1)


----------- Insert records into tblBranch--------------

INSERT INTO tblBranch VALUES(1,'IT')
INSERT INTO tblBranch VALUES(2,'CSE')
INSERT INTO tblBranch VALUES(3,'ECE')
INSERT INTO tblBranch VALUES(4,'ME')


tblStudent


tblBranch


1. Let's we create a simple CTE which is based on tblStudent:- 

  

With Student_Name_Gender
as
(
 Select Id, Name,Email,Gender from tblStudent
)
Select * from Student_Name_Gender



2. Let's we create, UPDATE Santosh's gender from Male to Female, using the Students_Name_Gender CTE 

  

With Student_Name_Gender
as
(
 Select Id, Name,Email, Gender from tblStudent
)
Update Student_Name_Gender Set Gender = 'Female' where Id = 1001




Now, query the tblStudent table. Santosh's gender is actually UPDATED. So, if a CTE is created on one base table, then it is possible to UPDATE the CTE, which in turn will update the underlying base table. In this case, UPDATING Student_Name_Gender CTE, updates tblStudent table.

3. Now, let's we create a CTE, on both the tables - tblStudent and tblBranch. This is display following output.



CTE, that returns Students by Branch 

  
With StudentsByBranch
as
(
 Select Id, Name, Email, Gender, BranchName 
 from tblStudent
 join tblBranch
 on tblBranch.BranchId = tblStudent.BranchId
)
Select * from StudentsByBranch


Let's update StudentsByBranch CTE. Let's change Santosh's Gender from Female to Male. Here, the CTE is based on 2 tables, but the UPDATE statement affects only one base table tblStudent. So the UPDATE succeeds. So, if a CTE is based on more than one table, and if the UPDATE affects only one base table, then the UPDATE is allowed. 

  

With StudentsByBranch
as
(
 Select Id, Name, Email, Gender, BranchName 
 from tblStudent
 join tblBranch
 on tblBranch.BranchId = tblStudent.BranchId
)

Update StudentsByBranch set Gender = 'Male' where Id = 1001




Now, let's try to UPDATE the CTE, in such a way, that the update affects both the tables - tblStudent and tblBranch. This UPDATE statement changes Gender from tblStudent table and BranchName from tblBranch table. When you execute this UPDATE, you get an error stating - 'View or function StudentsByBranch is not updatable because the modification affects multiple base tables'. So, if a CTE is based on multiple tables, and if the UPDATE statement affects more than 1 base table, then the UPDATE is not allowed. 

  
With StudentsByBranch
as
(
 Select Id, Name, Email, Gender, BranchName 
 from tblStudent
 join tblBranch
 on tblBranch.BranchId = tblStudent.BranchId
)

Update StudentsByBranch set Gender = 'Female', BranchName = 'ME' where Id = 1001


Finally, let's try to UPDATE just the BranchName. Let's change Santosh's BranchName from IT to CSE. Before, you execute the UPDATE statement, notice that Arun kumar and Arti are also currently in IT branch. 

  
With StudentsByBranch
as
(
 Select Id, Name, Email, Gender, BranchName 
 from tblStudent
 join tblBranch
 on tblBranch.BranchId = tblStudent.BranchId
)

Update StudentsByBranch set BranchName = 'CIVIL' where Id = 1001




After you execute the UPDATE. Select data from the CTE, and you will see that Arun kumar and Arti BranchName are also changed to CIVIL.

Why this is happen? This is because, when we updated the CTE, the UPDATE has actually changed the BranchName from IT to , in tblBranch table, instead of changing the BranchId column (from 1, 3 and 9) in tblStudent table. So, if a CTE is based on multiple tables, and if the UPDATE statement affects only one base table, the update succeeds. But the update may not work as you expect.