YT YouTube2Text

YouTube2Text → Transcripts → Updatable common table expressions in sql server Part 50

Updatable common table expressions in sql server Part 50 — Transcript

by kudvenkat · 2,385 words · 331 segments · language en · Watch on YouTube

Open in transcriber Summarize with AI Download SRT/VTT

Full transcript

0:00hello welcome to prume Technologies I
0:02wenet this is part 50 of SQL server in
0:05this session we'll learn about updatable
0:08common table Expressions before
0:10continuing with the session I strongly
0:12recommend to watch part 49 of this video
0:14series where we had discussed the basics
0:16of Common Table
0:18Expressions we know that a Common Table
0:21expression is a temporary result set it
0:24can be considered as a derived table now
0:28is it possible to update a CTE the
0:30answer is yes and no under certain
0:33circumstances you can update a CTE but
0:36under certain other circumstances you
0:39cannot do that we will explore the you
0:42know scenarios where we can update a CTE
0:45and also where we cannot let's look at
0:47an example we have this TBL employee
0:50table which has got ID name gender and
0:51Department ID columns now let's say we
0:54want to create a CTE on this table which
0:58returns just the ID name and gender that
1:01you can see on the right hand side here
1:03so obviously we know that to create a
1:05CTE we use the with keyword and then we
1:08G we are giving it a meaningful name
1:10employees name agender because this CT
1:13returns name and gender so employees
1:14name and gender as and then the CTE
1:17query itself so select what are the
1:20columns we require ID name gender so ID
1:22name gender from TBL employee so this is
1:24a simple select query which returns ID
1:26name gender from TBL employee table and
1:29you giving that temporary result set a
1:32name employees name gender that's about
1:36it so when you created the CTE all
1:38you're doing here is Select star from
1:41that CTE which would return ID name
1:43gender from TBL employee as you can see
1:45on the right hand side now is it
1:48possible to update this CTE absolutely
1:52let's look at that so here what you're
1:53doing I mean it's the same CTA you know
1:56this CTE is pretty much similar to this
1:58one except that here you're selecting
2:01from that CTE but here you're updating
2:04the CTE so you're saying with employee
2:06name gender as select ID name gender
2:09from TBL employee now instead of saying
2:11select star from employee name gender
2:14you're actually updating the CTE so when
2:17you update the CTE does this actually
2:20update the underlying table in this case
2:23TBL employee so here you're setting the
2:26gender to female for the record with ID
2:30is equal to 1 so if you look at the
2:32record here ID is equal to 1 is John's
2:36record and he is male at the moment but
2:38then when I update this you know we
2:41basically changing John's gender from
2:44male to female so when you update the
2:46CTE look at this we are not directly
2:48updating the table TBL employee we are
2:51updating the CTE so when you update the
2:54CTE does it actually update the TBL
2:56employee table yes it does so when you
2:59actually execute this update statement
3:02along with the CTA it actually changes
3:05John's gender from male to female and
3:08this is from TBL employees table this is
3:10the base table all right so if a CT is
3:15created on one base table here this CTE
3:17is based on just TBL employee table then
3:20it is possible to update the CTE which
3:23in turn will actually update the
3:25underlying base table in this case we
3:28are updating employee named gender the
3:30CTE which is actually updating TBL
3:33employee table let's look at this in
3:37action so we have this TBL employee and
3:39TBL departments tables but then we are
3:42going to create a CTE on TBL employee
3:44table now look at this the select query
3:47returns ID name gender from TBL
3:50employee okay so for this temporary
3:53result set you're just giving a name
3:55called employee name gender so obviously
3:59when you execute this since this CTE is
4:02returning ID name gender we get that now
4:05let's see how to update the CTE so you
4:08have the same CTE but instead of saying
4:11select star from that employee names
4:13genda you're actually saying update
4:15employee names uh employees name gender
4:18said gender is equal to female where ID
4:20is equal to one so currently John's
4:22gender is male we are basically changing
4:25that to female with this statement I
4:27mean with the CT so you are actually
4:30updating the CTE and this CTE should
4:32update the TBL employee table let's look
4:34at that one row affected now let's check
4:38the TBL employee table itself you should
4:40see now John's gender is
4:44female all right so if a CTE is based on
4:46one base table then it is possible to
4:49update the CTE which in turn will update
4:52the underlying base table now let's look
4:54at a scenario when a CTE is based on two
4:57base tables now I have two tables here
4:59TBL employee and TBL department and then
5:02I want to create a CTE which would
5:04return ID name gender from TBL employee
5:07table and Department name from TBL
5:09Department table so obviously this CTE
5:12has to have two base tables okay so we
5:16are creating employees by Department CTE
5:19here as what you are selecting here ID
5:22name gender Department name and this is
5:23a pretty simple joins query you're
5:26joining TBL employee with TBL department
5:29on Department ID column which is common
5:31between these two tables and we are
5:33basically selecting ID name gender and
5:34Department name columns okay so for this
5:37temporary result set we're actually
5:39giving a name employees by department so
5:43this CTA is based on two base tables now
5:48okay but then if you look at the update
5:50statement look at the update statement
5:52so update employees by Department which
5:54is nothing but the CTA so we are
5:55updating the
5:56CTA update the CTA set
6:00gender is equal to male though the CT is
6:03based on two base tables we are only
6:05updating gender which is coming from TBL
6:08employee table so you're only updating
6:11you know this update statement affects
6:14only TBL employee table you're actually
6:16changing the gender to male for record
6:19with ID is equal to one so here John
6:22gender is now female because we have
6:24changed that before we we are trying to
6:27change it back to male using this CTE so
6:31if a CTA is based on more than one table
6:34that's what is the situation here the
6:36CTE is based on two base tables and if
6:39the update affects only one base
6:42table then the update is allowed okay
6:46let's look at that in
6:50action so we have the city the same
6:52query that we saw on the slide we are
6:54selecting ID name gender from employee
6:57Department by joining them on the
6:59department ID column so that's the CD
7:03which should give us the output that we
7:04have actually seen on the slide so now
7:07let's try to change John's gender from
7:11female to male so instead of saying
7:14select star from that CTE you know the
7:17CTE is exactly same from here to here
7:20instead of Select we are updating that
7:22CTE so update employees by Department
7:25said gender is equal to male where ID is
7:27equal to one in this case ID is equal to
7:29one is John's record so let's update
7:35this one row affected now let's check
7:39the employees table if it has actually
7:41changed John's gender from male female
7:45to
7:50male so you should see that John's
7:53gender now changed from female to male
7:56so if a CTE is based on two tables and
7:59if the update statement affects only one
8:01base table then the update is
8:07allowed all right let's look at another
8:10scenario so the CT here is based on two
8:12tables just like before okay it's the
8:15same CTE but then if you look at the
8:18update statement it's updating the
8:20gender it's changing the gender to
8:22female and it's also changing the
8:24department name column so if you look at
8:27this in this CT it has got two columns
8:30name and gender uh I mean gender and
8:33Department name okay so gender and
8:35Department name we know that this gender
8:37is coming from TBL employee table
8:39Department name is coming from TBL
8:41Department table so obviously when you
8:43execute this update statement on this
8:46CTE this update will actually affect TBL
8:50employee and TBL Department okay both
8:53the tables so if a CTE is based on
8:57multiple tables and if the update state
8:59M affects more than one base table then
9:02the update statement is not allowed
9:04let's try to do this and see what
9:06actually
9:09happens so here we have a CT that's
9:12based on two tables the same CT that we
9:15have been working with TBL employee and
9:17TBL Department which returns ID name
9:19gender and Department name columns but
9:21then if you look at the update statement
9:23we are changing the
9:24gender and the department name so
9:27obviously this update statement affects
9:31TBL employee and TBL department so when
9:33we try to execute this let's see what's
9:35going to happen we get an error so view
9:39of function employees by department is
9:42not updatable because the modification
9:44affects multiple base tables so
9:47basically if a CTE is based on multiple
9:50statements uh multiple tables and if the
9:52update statement affects more than one
9:55base table then the update is not
9:57allowed there is one one more scenario
10:00before we conclude okay so if a CTA is
10:04again based on two tables two or more
10:06tables actually and then if the update
10:09affects only one base table let's
10:11understand the scenario it's the same
10:12CTE that we have been working with okay
10:16so ID name gender from TBL Department
10:19table and Department name from TBL
10:21Department table so obviously this CT is
10:24based on more than one base table now if
10:27you look at this update statement
10:30so update employees by Department set
10:33Department name is equal to it so
10:36basically what you are saying you know
10:38when you're saying update this employees
10:40by Department this is the CT and you're
10:43basically
10:44saying Department name is equal to it
10:48set Department name is equal to it where
10:50ID is equal to 1 so you're basically
10:52telling okay change John's Department
10:56name from HR to it B basically telling
10:59that but then before we execute this
11:01update query let's look at Ben's
11:03Department as well Ben is also in the HR
11:06department so Ben and John are in the HR
11:09department before we update before we
11:12executed this update query now as soon
11:15as I have updated this now we know that
11:18if a CTE is based on more than one based
11:22on you know multiple base tables and if
11:25the update statement affects only one
11:27base table then the update is allowed
11:29okay so obviously this update will
11:31succeed but then after you execute this
11:35it changes John's Department name to it
11:38which is good but then look at what
11:39happened to Ben's Department name it is
11:42also changed to ID before this update
11:44statement Ben's Department was HR and I
11:47told to change here only John's
11:52Department to it because I specified ID
11:55is equal to 1 iD is equal to 1 is John
11:57but why did Ben Department name change
12:00as soon as I have executed this update
12:02statement that is because you know here
12:06you're asking this update statement to
12:08actually change the department name to
12:10ID where ID is equal to one so John you
12:13know Department name is currently HR so
12:16what's going to happen is when this
12:18update statement is executed it actually
12:21changes you know look at this department
12:24number three is a Char okay which means
12:27John is currently has got Department ID
12:303 which means HR but when this statement
12:32is completed when when you execute this
12:35what is happening is it is actually
12:38changing you know this label from HR to
12:42it so it it will be like this the TBL
12:46Department table will be like this so
12:48number three it Department ID 3 is now
12:52changed from hr2 it so obviously anybody
12:55who has got three as the department ID
12:58all of them will be shown as if they
13:00belong to you know it Department that's
13:02the reason why Ben is also shown as if
13:05he belongs to I know it Department
13:09here okay so if a c is based on more
13:13than one p base table and if the update
13:17affects only one base one base table
13:19then update succeeds but not as expected
13:23always you know it may work correctly in
13:25some cases but in in this case the up is
13:29not working as we expect it to work Our
13:32intention is to basically change you
13:34know when you're are saying change the
13:36CTA such that you know the department
13:39name of John is changed from HR to it
13:42when you say that behind the scenes it
13:45should actually update you know John's
13:47Department ID from 3 to one because one
13:50is ID 3 is HR but instead what it did it
13:54actually changed the label name in TBL
13:56Department table from h are to it
14:00because of which any employee who has
14:02caught a department ID of three will now
14:05be shown as if they belong to the IT
14:07department which is
14:08incorrect we didn't expect it to do that
14:11so obviously when you update a CT there
14:14are several scenarios so if a CTE is
14:16based on a single base table then the
14:19update succeeds and works as expected
14:21always but if a CT is based on more than
14:24one base table and if the update affects
14:26multiple base tables the dat is not
14:29allowed and the statement terminates
14:31with an error if a CTE is based on more
14:34than one base table and if the update
14:36affects only one base table the update
14:40succeeds you know but not as expected
14:45always on this slide you can find
14:48resources for asp.net cop and SQL server
14:50interview questions if you want to
14:52receive email alerts when I upload new
14:55videos please subscribe to my YouTube
14:57channel that's it for today day thank
14:59you for listening have a great day

About this transcript

This page contains the full transcript of Updatable common table expressions in sql server Part 50 by kudvenkat, generated from the public captions YouTube serves with the video. The transcript has 2,385 words across 331 segments, with the original timestamps preserved so you can click any line to jump to that moment in the embedded player.

What you can do with it

Use the transcript to take notes, quote the speaker, build a study guide, generate a summary with ChatGPT or Claude via the YouTube Summary tool, or export it as a timed subtitle file with YouTube to SRT. You can also re-open it in the transcriber to translate the transcript into 100+ languages.

Free YouTube transcript tool

YouTube2Text is a free YouTube transcript generator — no signup, no daily limit. Paste any YouTube link and get the full transcript instantly, with timestamps, click-to-jump, translation to 100+ languages, AI prompts for ChatGPT, Claude, and Gemini, and exports to TXT, SRT, VTT, or Markdown.