title
Updatable common table expressions in sql server Part 50

description
In this video we will learn if it is possible to update a CTE. So in short if, 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) Text version of the video http://csharp-video-tutorials.blogspot.com/2012/09/updatable-cte-part-50.html Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help. https://www.youtube.com/channel/UC7sEwIXM_YfAMyonQCrGfWA/?sub_confirmation=1 Slides http://csharp-video-tutorials.blogspot.com/2013/09/part-50-updatable-ctes.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists

detail
{'title': 'Updatable common table expressions in sql server Part 50', 'heatmap': [{'end': 440.693, 'start': 414.532, 'weight': 1}, {'end': 611.393, 'start': 587.205, 'weight': 0.7}, {'end': 861.892, 'start': 840.545, 'weight': 0.894}], 'summary': 'Tutorial covers updatable common table expressions in sql server, including cte creation, updating, and scenarios for update statements, with examples provided for clarity and recommended references to prior parts.', 'chapters': [{'end': 48.607, 'segs': [{'end': 48.607, 'src': 'embed', 'start': 0.029, 'weight': 0, 'content': [{'end': 1.99, 'text': 'Hello, welcome to Prajeem Technologies.', 'start': 0.029, 'duration': 1.961}, {'end': 2.971, 'text': 'I am Venkat.', 'start': 2.35, 'duration': 0.621}, {'end': 5.252, 'text': 'This is part 50 of SQL Server.', 'start': 3.251, 'duration': 2.001}, {'end': 9.715, 'text': "In this session, we'll learn about updatable common table expressions.", 'start': 5.813, 'duration': 3.902}, {'end': 15.419, 'text': 'Before continuing with the session, I strongly recommend to watch part 49 of this video series,', 'start': 10.215, 'duration': 5.204}, {'end': 18.461, 'text': 'where we have discussed the basics of common table expressions.', 'start': 15.419, 'duration': 3.042}, {'end': 23.946, 'text': 'We know that a common table expression is a temporary result set.', 'start': 19.744, 'duration': 4.202}, {'end': 26.887, 'text': 'It can be considered as a derived table.', 'start': 24.366, 'duration': 2.521}, {'end': 32.57, 'text': 'Now, is it possible to update a CTE? The answer is yes and no.', 'start': 27.447, 'duration': 5.123}, {'end': 39.933, 'text': 'Under certain circumstances, you can update a CTE but under certain other circumstances, you cannot do that.', 'start': 32.97, 'duration': 6.963}, {'end': 46.896, 'text': 'We will explore the scenarios where we can update a CTE and also where we cannot.', 'start': 40.753, 'duration': 6.143}, {'end': 48.607, 'text': "Let's look at an example.", 'start': 47.546, 'duration': 1.061}], 'summary': 'Part 50 of sql server explores updatable common table expressions and scenarios for updating and not updating ctes.', 'duration': 48.578, 'max_score': 0.029, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/byU7a0WKPXo/pics/byU7a0WKPXo29.jpg'}], 'start': 0.029, 'title': 'Updatable common table expressions', 'summary': 'Covers updatable common table expressions in sql server, detailing scenarios for their update and recommending watching video part 49, with examples provided.', 'chapters': [{'end': 48.607, 'start': 0.029, 'title': 'Updatable common table expressions', 'summary': 'Covers updatable common table expressions in sql server, including scenarios where they can and cannot be updated, with a recommendation to watch the previous video, part 49, and an example provided.', 'duration': 48.578, 'highlights': ['The chapter covers updatable common table expressions in SQL Server.', 'It includes scenarios where they can and cannot be updated.', 'The recommendation to watch the previous video, part 49, is given.', 'An example of updatable common table expressions is provided.']}], 'duration': 48.578, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/byU7a0WKPXo/pics/byU7a0WKPXo29.jpg', 'highlights': ['The chapter covers updatable common table expressions in SQL Server.', 'It includes scenarios where they can and cannot be updated.', 'An example of updatable common table expressions is provided.', 'The recommendation to watch the previous video, part 49, is given.']}, {'end': 360.09, 'segs': [{'end': 95.569, 'src': 'embed', 'start': 49.027, 'weight': 0, 'content': [{'end': 53.191, 'text': 'We have this tblEmployee table which has got id, name, gender and department id columns.', 'start': 49.027, 'duration': 4.164}, {'end': 62.738, 'text': "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.", 'start': 53.591, 'duration': 9.147}, {'end': 70.307, 'text': 'So obviously we know that to create a CTE we use the with keyword And then we are giving it a meaningful name.', 'start': 63.279, 'duration': 7.028}, {'end': 74.111, 'text': 'employees name gender, because this CTE returns name and gender.', 'start': 70.307, 'duration': 3.804}, {'end': 78.716, 'text': 'So employees name and gender as and then the CTE query itself.', 'start': 74.171, 'duration': 4.545}, {'end': 81.96, 'text': 'So select what are the columns we require ID name gender.', 'start': 79.117, 'duration': 2.843}, {'end': 84.383, 'text': 'So ID name gender from TBL employee.', 'start': 82.2, 'duration': 2.183}, {'end': 88.808, 'text': 'So this is a simple select query which returns ID name gender from TBL employee table.', 'start': 84.643, 'duration': 4.165}, {'end': 95.569, 'text': "And you're giving that temporary result set a name, employees name gender.", 'start': 89.545, 'duration': 6.024}], 'summary': 'Creating a cte to return id, name, and gender from tblemployee.', 'duration': 46.542, 'max_score': 49.027, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/byU7a0WKPXo/pics/byU7a0WKPXo49027.jpg'}, {'end': 215.912, 'src': 'embed', 'start': 165.589, 'weight': 2, 'content': [{'end': 173.028, 'text': 'So when you update the CT, look at this, we are not directly updating table tblEmployee, we are updating the CTE.', 'start': 165.589, 'duration': 7.439}, {'end': 178.034, 'text': 'So when you update the CTE, does it actually update the tblEmployee table? Yes, it does.', 'start': 173.328, 'duration': 4.706}, {'end': 187.845, 'text': "So when you actually execute this update statement along with the CTE, it actually changes John's gender from male to female.", 'start': 178.835, 'duration': 9.01}, {'end': 190.608, 'text': "And this is from tblEmployee's table.", 'start': 187.985, 'duration': 2.623}, {'end': 191.649, 'text': 'This is the base table.', 'start': 190.648, 'duration': 1.001}, {'end': 193.545, 'text': 'All right.', 'start': 192.785, 'duration': 0.76}, {'end': 203.108, 'text': 'so if a CTE is created on one base table here the CTE is based on just TBL employee table then it is possible to update the CTE,', 'start': 193.545, 'duration': 9.563}, {'end': 207.049, 'text': 'which in turn will actually update the underlying base table.', 'start': 203.108, 'duration': 3.941}, {'end': 214.351, 'text': 'In this case, we are updating employee named gender CTE, which is actually updating TBL employee table.', 'start': 207.489, 'duration': 6.862}, {'end': 215.912, 'text': "Let's look at this in action.", 'start': 214.872, 'duration': 1.04}], 'summary': "Updating a cte can update the base table, changing john's gender from male to female.", 'duration': 50.323, 'max_score': 165.589, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/byU7a0WKPXo/pics/byU7a0WKPXo165589.jpg'}], 'start': 49.027, 'title': 'Cte creation and updating', 'summary': 'Covers creating a cte on tblemployee table to return specific columns using the with keyword and updating a cte based on one or two base tables, exemplified by changing a record in the underlying base table.', 'chapters': [{'end': 95.569, 'start': 49.027, 'title': 'Creating cte on tblemployee table', 'summary': 'Explains how to create a common table expression (cte) on the tblemployee table, which returns the columns id, name, and gender by using the with keyword and a meaningful name, to select the required columns from the table.', 'duration': 46.542, 'highlights': ['To create a CTE on the tblEmployee table, use the with keyword and give it a meaningful name, such as employees name gender, for the CTE that returns name and gender.', 'The CTE query should select the required columns ID, name, and gender from the TBL employee table.']}, {'end': 360.09, 'start': 95.969, 'title': 'Updating common table expressions (cte)', 'summary': "Explains how to update a common table expression (cte) based on one and two base tables, demonstrating that updating the cte can update the underlying base table, with a specific example showing a change in john's gender from male to female.", 'duration': 264.121, 'highlights': ["When you update the CTE, it actually changes John's gender from male to female, demonstrating that updating the CTE can update the tblEmployee table. Updating the CTE changes John's gender from male to female, directly updating the tblEmployee table.", 'The chapter illustrates how to update a CTE based on two base tables, tblEmployee and tblDepartment, and shows that updating the CTE can update the underlying base table. Demonstrating the process of updating a CTE based on two base tables and how it can update the underlying base table.', "Explaining the process of creating and updating a CTE, showing the impact on the underlying base table, with a specific example of updating John's gender from male to female. Providing an overview of creating and updating a CTE, with a specific example of updating John's gender from male to female."]}], 'duration': 311.063, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/byU7a0WKPXo/pics/byU7a0WKPXo49027.jpg', 'highlights': ['To create a CTE on the tblEmployee table, use the with keyword and give it a meaningful name, such as employees name gender, for the CTE that returns name and gender.', 'The CTE query should select the required columns ID, name, and gender from the TBL employee table.', "When you update the CTE, it actually changes John's gender from male to female, demonstrating that updating the CTE can update the tblEmployee table.", 'The chapter illustrates how to update a CTE based on two base tables, tblEmployee and tblDepartment, and shows that updating the CTE can update the underlying base table.', "Explaining the process of creating and updating a CTE, showing the impact on the underlying base table, with a specific example of updating John's gender from male to female."]}, {'end': 900.772, 'segs': [{'end': 440.693, 'src': 'heatmap', 'start': 414.532, 'weight': 1, 'content': [{'end': 420.773, 'text': 'we are selecting ID, name, gender from employee department by joining them on the department ID column.', 'start': 414.532, 'duration': 6.241}, {'end': 426.296, 'text': "So that's the CD which should give us the output that we have actually seen on the slide.", 'start': 421.488, 'duration': 4.808}, {'end': 432.107, 'text': "So now, let's try to change John's gender from female to male.", 'start': 426.983, 'duration': 5.124}, {'end': 440.693, 'text': 'So instead of saying, select star from that CTE, you know, the CTE is exactly same from here to here.', 'start': 432.507, 'duration': 8.186}], 'summary': 'Query selects id, name, gender, and performs gender update in cte.', 'duration': 26.161, 'max_score': 414.532, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/byU7a0WKPXo/pics/byU7a0WKPXo414532.jpg'}, {'end': 500.908, 'src': 'embed', 'start': 476.178, 'weight': 0, 'content': [{'end': 484.362, 'text': 'So if a CTE is based on two tables, and if the update statement affects only one base table, then the update is allowed.', 'start': 476.178, 'duration': 8.184}, {'end': 490.901, 'text': "Alright, let's look at another scenario.", 'start': 488.359, 'duration': 2.542}, {'end': 494.383, 'text': 'So the CTE here is based on two tables, just like before.', 'start': 491.161, 'duration': 3.222}, {'end': 496.425, 'text': "It's the same CTE.", 'start': 494.804, 'duration': 1.621}, {'end': 500.908, 'text': "But then, if you look at the update statement, it's updating the gender,", 'start': 496.845, 'duration': 4.063}], 'summary': 'Cte based on two tables allows update of one base table.', 'duration': 24.73, 'max_score': 476.178, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/byU7a0WKPXo/pics/byU7a0WKPXo476178.jpg'}, {'end': 611.393, 'src': 'heatmap', 'start': 587.205, 'weight': 0.7, 'content': [{'end': 597.99, 'text': 'So basically, if a CTE is based on multiple tables, and if the update statement affects more than one base table, then the update is not allowed.', 'start': 587.205, 'duration': 10.785}, {'end': 601.171, 'text': 'There is one more scenario before we conclude.', 'start': 598.69, 'duration': 2.481}, {'end': 611.393, 'text': '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?', 'start': 603.166, 'duration': 8.227}], 'summary': 'Cte update not allowed on multiple tables, exception for single table update', 'duration': 24.188, 'max_score': 587.205, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/byU7a0WKPXo/pics/byU7a0WKPXo587205.jpg'}, {'end': 871.996, 'src': 'heatmap', 'start': 840.545, 'weight': 1, 'content': [{'end': 848.66, 'text': 'because of which any employee who has got a department ID of 3 will now be shown as if they belong to the IT department, which is incorrect.', 'start': 840.545, 'duration': 8.115}, {'end': 851.188, 'text': "We didn't expect it to do that.", 'start': 849.747, 'duration': 1.441}, {'end': 855.469, 'text': 'So obviously, when you update a CTE, there are several scenarios.', 'start': 851.748, 'duration': 3.721}, {'end': 861.892, 'text': 'So if a CTE is based on a single base table, then the update succeeds and works as expected always.', 'start': 855.509, 'duration': 6.383}, {'end': 868.535, 'text': 'But if a CTE is based on more than one base table and if the update affects multiple base tables,', 'start': 862.532, 'duration': 6.003}, {'end': 871.996, 'text': 'the update is not allowed and the statement terminates with an error.', 'start': 868.535, 'duration': 3.461}], 'summary': 'Updating cte with multiple base tables can lead to termination with an error', 'duration': 48.989, 'max_score': 840.545, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/byU7a0WKPXo/pics/byU7a0WKPXo840545.jpg'}], 'start': 360.09, 'title': 'Cte update statement scenarios and issues', 'summary': 'Discusses scenarios of using common table expressions (cte) for update statements, specifying that updates involving one base table are allowed, while updates involving multiple base tables are not. it also explains issues encountered with cte update statements, illustrating an unexpected change in department names and ids for employees.', 'chapters': [{'end': 626.566, 'start': 360.09, 'title': 'Cte update statement scenarios', 'summary': 'Discusses the scenarios of using common table expressions (cte) for update statements, outlining that if a cte is based on two tables and the update statement affects only one base table, the update is allowed; however, if the update statement affects more than one base table, the update is not allowed.', 'duration': 266.476, 'highlights': ['If a CTE is based on two tables and the update statement affects only one base table, the update is allowed. When a CTE is based on two tables and the update statement affects only one base table, the update is allowed, as demonstrated by changing the gender field for a specific record.', 'If the update statement affects more than one base table, the update is not allowed. If a CTE is based on multiple tables and the update statement affects more than one base table, the update is not allowed, as shown by attempting to update both the gender and department name fields, resulting in an error.', 'CTE based on multiple tables is not updatable if the update affects more than one base table. A CTE based on multiple tables is not updatable if the update affects more than one base table, leading to an error message indicating that the modification affects multiple base tables.']}, {'end': 900.772, 'start': 627.086, 'title': 'Cte update statement issues', 'summary': 'Explains the issues encountered with the cte update statement, demonstrating a scenario where the update affects only one base table but does not work as expected, leading to unintended changes in department names and ids for employees.', 'duration': 273.686, 'highlights': ['When a CTE is based on more than one base table, and the update affects only one base table, the update may not work as expected, as demonstrated by the scenario where the department name and ID changes for employees did not occur as intended. CTE update affecting one base table', 'The unintended changes in department names and IDs for employees occurred due to the update statement changing the label name in the TBL department table, resulting in incorrect department associations for employees. Unintended department name and ID changes', "The update statement did not work as expected, leading to incorrect department associations for employees with a department ID of 3, which was updated to show them as belonging to the IT department, contrary to the intended change for John's department only. Incorrect department associations for employees"]}], 'duration': 540.682, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/byU7a0WKPXo/pics/byU7a0WKPXo360090.jpg', 'highlights': ['If a CTE is based on two tables and the update statement affects only one base table, the update is allowed.', 'If the update statement affects more than one base table, the update is not allowed.', 'CTE based on multiple tables is not updatable if the update affects more than one base table.', 'The unintended changes in department names and IDs for employees occurred due to the update statement changing the label name in the TBL department table.', 'The update statement did not work as expected, leading to incorrect department associations for employees with a department ID of 3.']}], 'highlights': ['The chapter illustrates how to update a CTE based on two base tables, tblEmployee and tblDepartment, and shows that updating the CTE can update the underlying base table.', "Explaining the process of creating and updating a CTE, showing the impact on the underlying base table, with a specific example of updating John's gender from male to female.", "When you update the CTE, it actually changes John's gender from male to female, demonstrating that updating the CTE can update the tblEmployee table.", 'The chapter covers updatable common table expressions in SQL Server.', 'It includes scenarios where they can and cannot be updated.', 'An example of updatable common table expressions is provided.', 'To create a CTE on the tblEmployee table, use the with keyword and give it a meaningful name, such as employees name gender, for the CTE that returns name and gender.', 'The CTE query should select the required columns ID, name, and gender from the TBL employee table.', 'If a CTE is based on two tables and the update statement affects only one base table, the update is allowed.', 'If the update statement affects more than one base table, the update is not allowed.', 'CTE based on multiple tables is not updatable if the update affects more than one base table.', 'The unintended changes in department names and IDs for employees occurred due to the update statement changing the label name in the TBL department table.', 'The update statement did not work as expected, leading to incorrect department associations for employees with a department ID of 3.', 'The recommendation to watch the previous video, part 49, is given.']}