How to update table using CTE in sql server

In this session, we will learn about updatable common table expressions. Before continuing with this post, I strongly recommend reading the previous post of the CTE series, where we discussed the basics of common table expressions.

• What is CTE in sql server with example

We know that a common table expression is a temporary ResultSet. It can be considered as a derived table. Now, is it possible to update a CTE? The answer is yes and no.

Under certain circumstances, you can update a CTE, but under certain circumstances, you cannot do that. We will explore the scenarios where we can update the CTE and also where we cannot.

Let’s look at an example. We have the TblProfessor table, which has Location, Name, Gender, and University_Id columns.

And then I have the TblUniversity, which has Id and UniversityName columns. TblProfessor and TblUniversity are related using the foreign key University_Id.

Table Script

/****** Object:  Table [dbo].[TblProfessor]    Script Date: 09/22/2020 6:55:32 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblProfessor](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](max) NOT NULL,
    [Gender] [nvarchar](50) NULL,
    [University_Id] [int] NULL,
 CONSTRAINT [PK_TblStudent] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)
)
GO
/****** Object:  Table [dbo].[TblUniversity]    Script Date: 09/22/2020 6:55:33 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblUniversity](
    [University_Id] [int] IDENTITY(1,1) NOT NULL,
    [UniversityName] [nvarchar](max) NULL,
 CONSTRAINT [PK_University] PRIMARY KEY CLUSTERED 
(
    [University_Id] ASC
)
)
GO
SET IDENTITY_INSERT [dbo].[TblProfessor] ON 
GO
INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [University_Id]) VALUES (1, 'Mark', 'Male', 2)
GO
INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [University_Id]) VALUES (2, 'Emma', 'Female', 2)
GO
INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [University_Id]) VALUES (3, 'Advika', 'Female', 4)
GO
INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [University_Id]) VALUES (4, 'Jack', 'Male', 3)
GO
INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [University_Id]) VALUES (5, 'Adweta', 'Female', 6)
GO
INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [University_Id]) VALUES (6, 'Jemmy', 'Male', 5)
GO
INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [University_Id]) VALUES (7, 'Kerlin', 'Female', 7)
GO
INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [University_Id]) VALUES (8, 'Laar', 'Male', 3)
GO
SET IDENTITY_INSERT [dbo].[TblProfessor] OFF
GO
SET IDENTITY_INSERT [dbo].[TblUniversity] ON 
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (1, 'Carnegie Mellon University')
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (2, 'Michigan State University')
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (3, 'Arizona State University')
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (4, 'The Australian National University')
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (5, 'The University of Arizona')
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (6, 'The University of Melbourne')
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (7, 'McGill University')
GO
SET IDENTITY_INSERT [dbo].[TblUniversity] OFF
GO
ALTER TABLE [dbo].[TblProfessor]  WITH CHECK ADD  CONSTRAINT [FK_TblStudent_University] FOREIGN KEY([University_Id])
REFERENCES [dbo].[TblUniversity] ([University_Id])
GO
ALTER TABLE [dbo].[TblProfessor] CHECK CONSTRAINT [FK_TblStudent_University]
GO

Now, let’s say we want to create a CTE on this table which returns just the Id, name, and gender that you can see on the below image.

With TblProfessor_Detail
as
(
Select Id, Name, Gender from [dbo].[TblProfessor]

)
Select * from TblProfessor_Detail

We know that to create a CTE, we use the “with” keyword, and then we give it a meaningful name, TblProfessor_Detail, followed by the "AS" keyword and the CTE Query itself.

What are the columns we require? So this is a simple select query which returns Id, name, gender from the TblProfessor table. And you’re giving that temporary ResultSet in TblProfessor_Detail.

Now, is it possible to update this CTE? Absolutely. Let’s look at the below query.

With TblProfessor_Detail
as
(
Select Id, Name, Gender from TblProfessor
)
Update TblProfessor_Detail Set Name = 'U_shiveam' where Id = 1

So here you are updating the CTE. When you update the CTE, does this actually update the underlying table?

Here you are setting the Name to ‘U shiveam’ for the record with Id equal to 1. So if you look at the record in the table, where Id is 1, it is Shiveam’s record.

But then when I update this, we are basically changing Shiveam’s name from “Shiveam” to “U_shiveam”. We are not directly updating the table TblProfessor. We are updating the CTE.

So when you update the CTE, does it actually update the TblProfessor? Yes, it does. When you execute this update statement along with the CTE, it actually changes Shiveam’s name from “Shiveam” to “U_shiveam”.

Now let’s check the TblProfessor table itself. You should see how the record is updated now.

So if a CTE is created based on one base table, then it is possible to update the CTE. Here, the CTE is based solely on the TblProfessor table, which will actually update the underlying base table.

CTE based on two base tables

Now, let's consider a scenario where a Common Table Expression (CTE) is based on two base tables, namely TblProfessor and TblUniversity.

I would like to create a CTE that retrieves the ID, name, and gender from the TblProfessor table, along with the University name from the TblUniversity table. Since this CTE requires data from two base tables, it needs to incorporate both tables in its definition.

With TblProfessor_Department
as
(
Select Id, Name, Gender, UniversityName
from TblProfessor
join TblUniversity
on TblUniversity.University_Id = TblProfessor.University_Id
)
Select * from TblProfessor_Department

This is a straightforward JOIN query. It involves joining TblProfessor with TblUniversity based on the common column University_Id, and we are selecting columns Id, name, gender, and University name.If you're unfamiliar with Joins, please read the article below.

• What is Sql Joins? With Realtime Examples

With TblProfessor_Department
as
(
Select Id, Name, Gender, UniversityName
from TblProfessor
join TblUniversity
on TblUniversity.University_Id = TblProfessor.University_Id
)
Update TblProfessor_Department set Name = 'Mark' where Id = 1

So we're updating TblProfessor_Department, which is essentially the CTE we're working with. We're only updating the 'Name' column, which originates from TblProfessor. Therefore, this update statement only impacts the TblProfessor table.

Specifically, we're changing the name to 'Mark' for the record with an ID of 1.

When a CTE is based on more than one table, and if the update only affects one base table, then the update is permitted.

Let's execute the above query and verify the changes in the database.

You should observe that the record with ID=1 has now been changed to 'Mark'. So, if a CTE is based on two tables and the update statement affects only one base table, then the update is allowed.

Now, let's consider another scenario. Using the same CTE, I want to update both the name and UniversityName as well.

With TblProfessor_Department
as
(
Select Id, Name, Gender, UniversityName
from TblProfessor
join TblUniversity
on TblUniversity.University_Id = TblProfessor.University_Id
)
Update TblProfessor_Department set Name = 'U_Mark', UniversityName = 'University of Oxford' where Id = 1

So, obviously, when you execute this update statement on this CTE, it will affect both TblProfessor and TblUniversity tables.

If a CTE is based on multiple tables and the update statement affects more than one base table, then the update statement is not allowed.

Let's attempt to execute this and observe what happens. You will receive the following error.

“Msg 4405, Level 16, State 1, Line 1
View or function ‘TblProfessor_Department’ is not updatable because the modification affects multiple base tables.”

When a Common Table Expression (CTE) is based on multiple tables and the update statement impacts more than one of these base tables, the update operation is not allowed. This limitation exists to maintain data integrity and prevent unexpected side effects that could arise from modifying multiple tables simultaneously through a single CTE-based update statement.