Updatable common table expressions in sql server Part 50 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

hello welcome to presume technologies I
am venket this is part 50 of sequel
server in this session we'll learn about
updatable common table expressions
before continuing with the session I
strongly recommend to watch part 49 of
this video series where we have
discussed the basics of common table
expressions we know that a common table
expression is a temporary result set it
can be considered as a derived table now
is it possible to update a CT the answer
is yes and no under certain
circumstances you can update the CTE but
under certain other circumstances you
cannot do that we will explore the you
know scenarios where we can update a CD
and also where we cannot let's look at
an example we have this table employee
table which has got ID name gender and
department ID columns 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 right hand side here
so obviously we know that to create a
CTE we use the width keyword and then we
give me we are giving it a meaningful
name employee's name agenda because this
CTE returns name in gender so employees
name in gender as and then the CTE query
itself so select what are the columns we
require ID name gender so ID name gender
from TBL employee so this is a simple
select query which returns ID name
gender from TBL employee table and you
are giving that temporary results at in
named employees name gender that's about
it so when you created the CTE all you
are doing here is select star from that
CTE which would return ID name gender
from TBL employee as you can see on the
right hand side now is it possible to
update this CTE
absolutely let's look at that so here
what you are doing I mean it's the same
CTE you know this CTE is pretty much
similar to this one except that here you
are selecting from that CD but here you
are updating the city so you are saying
with employee name gender as select ID
name gender from TBL employee now
instead of saying select star from
khloé name gender you are actually
updating the CTE so when you update the
CTE does this actually update the
underlying table in this case TBL
employee so here you are setting the
gender to female for the record with ID
is equal to one so if you look at the
record here ID is equal to one is John's
record and he is male at the moment but
then when I update this you know we're
basically changing John's gender from
male to female so when you update the
CTE look at this we are not directly
updating the table TBL employee we are
updating this CTE
so when you update the CTE does it
actually update the TBL employee table
yes it does
so when you actually execute this update
statement along with the CTE it actually
changes John's gender from male to
female and this is from TBL employees
table this is the base table alright so
if a CT is created on one base table
here this CTE is based on just TBL
employee table then it is possible to
update the CTE which in turn will
actually update the underlying base
table in this case we are updating
employee named gender CTE which is
actually updating TBL employee table
let's look at this in action so we have
this TBL employee in TBL departments
tables but then we are going to create a
CT on TBL employee table now look at
this the select query returns ID name
gender from TBL employee okay so for
this temporary result set you are just
giving a name called employee named
gender so obviously when you execute
this since this CTE is returning ID
named gender we get that now let's see
how to update the CD so you have the
same CTE but instead of saying select
star from that employee names gender you
are actually saying update employee
names employees name gender set gender
is equal to female wear ID is equal to
one so currently Joan's gender is male
we are basically changing that to female
with this statement I mean with the CT
so you are actually updating the CTE the
CT should update the TBL employee table
let's look at that one row affected now
let's check the TBL employee table
itself you should see now Jones gender
is female alright so if a CTE is based
on one base table then it is possible to
update the CT which in turn will update
the underlying base table now let's look
at a scenario when a CTE is based on two
base tables now I have two tables here
TBL employee and TBL department and then
I want to create a CTE which would
return ID name gender from TBL employee
table and Department name from TBL
department table so obviously this CTE
has to have two base tables okay so we
are creating employees by Department CT
here as what you are selecting here ID
name gender Department name and this is
pretty simple joins query you're joining
TBL employee with TBL department on
department ID column which is common
between these two tables and we are
basically selecting ID name gender and
department name columns okay so for this
temporary result set we are actually
giving your name employees by department
so this CTE is based on two base tables
now okay but then if you look at the
update statement look at the update
statement so update employees by
department which is nothing but the CT
so we are updating the CTE update the
CTE set gender is equal to male though
the CT is based on two base tables we
are only updating gender which is coming
from TBL employee table so you are only
updating you know this update statement
affects only TBL employee table you are
actually changing the gender to male for
record with ID is equal to one so here
John gender is now female because we
have changed that before we are trying
to change it back to male using this CTE
so if a CTA is based on more than one
table that's what is the situation here
the city is based on two base tables
and if the update affects only one base
table then the update is allowed okay
let's look at that in action so we have
the city the same query that we saw on
the slide we are selecting ID name
gender from employee department by
joining them on the department ID column
so that's the CD which should give us
the output that we have actually seen on
the slide so now let's try to change
John's gender from female to male
so instead of saying select star from
that CTE you know the city is exactly
same from here to here instead of select
we are updating that city so update
employees by Department said gender is
equal to male wear ID is equal to 1 in
this case ID is equal to one is John's
record so let's update this one row
affected now let's check the employees
table if it has actually changed
John's gender from male female to male
so you should see that John's gender now
changed from female to male so if a CDE
is based on two tables and of the update
statement of X only one base table then
the update is allowed alright let's look
at another scenario so the CT here is
based on two tables just like before
okay it's the same CDE but then if you
look at the update statement it's
updating the gender it's changing the
gender to female and it's also changing
the department name column so if you
look at this in this CT it has got two
columns name and gender are I mean
gender and department name okay so
gender and department name we know that
this gender is coming from TBL employee
table Department name is coming from TBL
department table so obviously when you
execute this update statement on this
CTE this update will actually affect TBL
employee and TBL department
okay both the tables so if a CTE is
based on multiple tables and if the
update statement affects more than one
base table then the update statement is
not allowed let's try to do this and see
what actually happens so here we have a
CD that's based on two tables the same
CD that we have been working with TBL
employee and TBL department which
returns ID name gender and department
name columns but then if we look at the
update statement we are changing the
gender and the department name
so obviously this update statement
affects TBL employee and TBL department
so when we try to execute this let's see
what's going to happen we get an error
so view of function employees by
Department is not updatable because the
modification affects multiple base
tables so basically if a CTE is based on
multiple statements multiple tables and
if the update statement affects more
than one base table then the update is
not allowed there is one more scenario
before we conclude okay so if a CTE is
again based on two tables two or more
tables actually and then if the update
affects only one base table let's
understand the scenarios the same CTE
that we have been working with okay so I
rename gender from TBL Dept table and
Department name from TBL Dept table
so obviously this CT is based on more
than one base table now if you look at
this update statement
so update employees by Department set
Department name is equal to I T so
basically what you are saying you know
when you're saying update this employees
by Department this is the CT and you're
basically saying department name is
equal to I T set a department name is
equal to I T V ID is equal to 1 so
you're basically telling ok change
Joan's
department name from HR to IT you're
basically telling that but then before
we execute this update query let's look
at Benz department as well
is also in the HR department so Ben and
John are in the HR department before we
update before we executed the subject
query now as soon as I have updated this
now we know that if a CTE is based on
more than one based on you know multiple
based tables and if the update statement
affects only one base table then the
update is allowed okay so obviously this
update will succeed but then after you
execute this it changes John's the
department name to IT which is good but
then look at what happened to Ben's
department name it is also change it to
ID before this update statement Ben's
department was HR and I told to change
here only
John's Department to IT because I
specified ID is equal to one ID is equal
to one as John but why did Ben's
department name change as soon as I have
executed this update statement that is
because you know here you are asking
this update statement to actually change
the department name to IT where ID is
equal to one
so John you know Department name is
currently HR so what's gonna happen is
when this update statement is executed
it actually a changes you know look at
this department number three is HR ok
which means John is currently has got
department ID 3 which means HR but when
this statement is completed when when
you execute this what is happening is it
is actually changing you know this label
from HR to I T so it will be like this
the TBL department table will be like
this so number 3 IT department ID 3 is
now changed from HR to IT so obviously
anybody who has got 3 as the department
ID all of them will be shown as if they
belong to you know IT department that's
the reason why Ben is also shown as if
he belongs to I know
IT department here ok so if a CT is
based on more than one base table and if
the update affects only one based one
bass table then update succeeds but not
as expected always you know it may work
correctly in some cases but in in this
case the update is not working as we
expect it to work our intention is to
basically change you know when you are
saying change the CTA such that you know
the department name of John is changed
from HR to IT when you say that behind
the scenes it should actually update you
know John's department ID from three to
one because one is ID three is HR but
instead what it did it actually changed
the label name in TBL department table
from HR to IT because of which any
employee who has caught a department ID
of three will now be shown as if they
belong to the IT department which is
incorrect we didn't expect it to do that
so obviously when you update a city
there are several scenarios so if a CTE
is based on a single base table then the
update succeeds and works as expected
always but if 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 if a CTE is
based on more than one base table and if
the update affects only one base table
the update succeeds you know but not as
expected always on this slide you can
find resources for asp.net c-sharp and
sequel server interview questions if you
want to receive email alerts when I
upload new videos please subscribe to my
youtube channel that's it for today
thank you for listening have a great day