How to update table using CTE in sql server

Welcome to appsloveworld Technologies, In the session, we learn about updatable common table expressions before continuing with this post. I strongly recommend reading the previous post of the CTE series. where we have 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 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 TblProfessor table. We got  Location, Name, Gender, and University_Id columns.

And then I have the TblUniversity which has got the Id,UniversityName columns. TblProfessor and TblUniversity  are related using 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

So obviously, we know that to create a CTE, we use the “with” keyword, and then we are giving it a meaningful name, TblProfessor_Detail. “As” and then CTE Query itself.

what are the columns we require? So this is a simple select query which returns Id, name, gender from [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 what you are doing. 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 is equal to 1. So if you look at the record in the table, Id=1 is shiveam’s record.

But then when I update this, we 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 theTblProfessor? Yes, it does. So when you actually 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 on one base table then it is possible to update the CTE, here the CTE is based on just the TblProfessor table.which will actually update the underlying base table.

CTE based on two base tables

Now, let’s look at a scenario when a CTE is based on two base tables. Now, I have two tables TblProfessor and  TblUniversity.

I want to create a CTE which would return I’d name gender from the TblProfessor table and University name from the TblUniversity table.So obviously this CTE has to have to base tables.

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 pretty simple JOIN query. You are joining TblProfessor with TblUniversity on the University_Id column, which is common between these two tables and we are basically selecting Id name gender, and University name columns.

If you are not familiar with Join ,please read below article.

• 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 update TblProfessor_Department, which is nothing but the CTE. we are updating the CTE.

We are only updating Name, which is coming from TblProfessor. So this update statement affects only the TblProfessor table.

You’re actually changing the name to ‘Mark‘ for the record, with id=1.

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

So let’s execute above and check the database

you should see that record with id=1, now changed to “Mark“, so if a CTE is based on two tables and if the update statement affects only one base table, then the update is allowed.

All right, let’s look at another scenario. using the same CTE I want to update the name and UniversityName also.

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, this update will affect TblProfessor and TblUniversity both the tables.

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

Let’s try to do this and see what actually happens, let’s execute the above query. you will get the below error.
“Msg 4405, Level 16, State 1, Line 1
View or function ‘TblProfessor_Department’ is not updatable because the modification affects multiple base tables.”

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

The post How to update table using CTE in sql server appeared first on Software Development | Programming Tutorials.