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.
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.
/****** 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.
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.
Read More Articles
- Linq to SQL Group by and Sum in Select | Linq To SQL Group By and Sum
- How send an HTTP POST request to a server from Excel using VBA?
- What is Difference between where and having clauses in SQL server
- How to Use EXCEPT Operator with an example in Sql
- How to use merge statement in the SQL server
- How to write Re-runnable SQL server query
- How to create Cursor in sql server with example
- How to generate random data in sql server for performance testing
- How to write subquery in select statement in Sql
- How to Log record changes in SQL server in an audit table
- Query Results Not Expected
- within substring method how to use Isnull()
- SQL Server migrating database performance what is better
- I am running a web app and want to update my SQL Server user profile database with C# code but no matter what I do I cannot get it to work
- Does CROSS APPLY WHERE clause work before cross apply or after on the results
- Combining two tables with date ranges into one table
- linked server, open query using variable for date field
- Escaping function wiping all data from text fields/areas
- Update a variable number of parameters in a Stored Procedure
- How can i configure sql server 2008 using powershell?
- SQL - Insert multiple rows based on one record with conditions
- C# Delete problem with an object relating to another object
- Windows Server: Number of simultaneous connections for IIS and SQL Server
- Delete from table, and return the records which weren't deleted
- For Nvarchar(Max) I am only getting 4000 characters in TSQL?
- Error when inserting in to SQL Server through System.Data.Linq.DataContext
- SQL Server WHERE Clause using Temporary Columns
- Sql Server Order Max Values
- SQL comma separated column loop
- sql select in string with a split function?
- Use Sql LIKE in TableAdapter for int values
- How to retrieve some information from a column using SQL
- Compilation when Column doesn't exist
- How can I make a SQL temp table with primary key and auto-incrementing field?
- cmd line SQL server 2008 express install
- Advice on Azure platform to host Data Warehouse
- why all columns are invalid after table rename?
- Calculate new rows (or columns) based previously calculated rows (or columns) in same select statement
- Get the SQL Server Agent job that has run a particular execution of a deployed SSIS package
- Update old table data with new table data from different database
- Encrypt the stored procedure created by SqlDependency
- Foreign-Key constraint depending on category
- SQL Server - Constraint to prevent two columns having specific values at the same time
- Select and Data-Retrieval Operations Error
- SQL Query to split data based on column value
- How to make the smart box in SQL server appear when typing?
- Filter SQL Data to get latest for each alternate ID using MSSQL
- Python & SQL Server stored procedure hang
- CURRENT_UTC_TIME and timezone issues
- How can we track stored procedure changes in SQL Server like version control in TFS
- SQL use of CASE to define where clauses
- Performance of nested select and DISTINCT/ORDER
- How to fix a restoring database without the backup file in SQL Server?
- To get date when column value changes from no to yes in SQL Server database
- Deploying WPF applications with SQL Server
- Sql double split on delimiter
- SQL SELECT based on joined table
- SQL Server: help with a simple stored procedure?
- C#, EF & LINQ : slow at inserting large (7Mb) records into SQL Server
- Data from two years in a row
- SQL trace in Great Plains shows Invalid column name 'desSPRkmhBBCreh'
- JDBC Template : MS SQL datetime column query
- Get data on the basis of comma-separated ids present in row - SQL
- How to update SQL rows based on other rows with shared ID?
- Question mark issue with ADOdb and MSSQL
- Forward DB requests after a DB move in SQL Server
- best way to migrate a windows forms application from sql server to oracle
- ASP.net unable to attach file as database
- What is a pseudo-merge join?
- Get no. of rows returned by stored procedure in SQL Server Reporting Services
- How to use the EXEC command
- sql declare string and use in where clause
- SQL Server keys issue
- Display date in MM-dd-yyyy format SSRS report
- Docker SQL Server exception
- PIVOT / UNPIVOT
- Simple way to select a column from all databases on a server instance in SQL Server 2014 Management Studio?
- grouping items in sql query
- What is wrong with my code, meaning that no details entered still logs the user in?
- SQL Server Maximum row size
- Splitting multiple fields by delimiter
- SQL Server, calculating total hours per day with multiple breaks
- Microsoft Visual Studio - Unable to open script task in SSIS Package
- Can I develop in SQL Server Express for later deployment in a full SQL Server?
- SQL: Select TOP N Marks Per User (In a List of Users)
- How to handle in an effective way duplicate key insertions w/out throwing an exception
- Transform SQL Query Results
- SQL import only different records
- Filestream column in the WHERE clause locks the server
- How to implement sharding?
- Update records only when ID matches
- SQL, C# - Can't convert int32 to string?
- No column name showing up in SQL
- Finding the null column values
- Which DBCC commands do I need to run to make sure SQL Server's caches are totally clean?
- How to parse a primary key
- How to convert Varchar "YYYYMMDDHHMMSS" to Datetime in SQL Server?
- OLEDB 12.0 is not installed on local machine (ACE Driver)
- SQL Server 2014 execution plan creation takes a long time (fast in older versions)
- How to update values in a current table with a return value from a dynamic query?
- ms sql row_number() function - won't let me use within the same statement
- How to copy table data from one SQL Server into another SQL server?
- Sql server bcp export binary in csv, How do I replace it with ''?"
- How to group data based on continuous date range?
- SQL Server XML Data Type query issue
- SQL Server : Unique constraint to verify that data does not exist in another table
- Update column in SQL Server 2008
- Does not have a primary key defined and no valid primary key could be inferred
- Insert using variables and another table's column
- SQL Server Agent permissions versus user permissions
- Rownumber to select non duplicate or distinct rows from a table. Invalid name ''
- SqlCommand closing connections properly
- Query relations within the same table
- SQL Server : grabbing DISTINCT column and the rest
- Is there any way to pass connection string (connection manager) for SSIS package while adding job in command parameter
- T-SQL, Find numeric values
- Shortening GROUP BY Clause
- SQL Server: One Table with 400 Columns or 40 Tables with 10 Columns?
- Very slow DELETE query
- Querying attributes and node values from XML stored in an XML data type
- ''geometry::Point' failed because parameter 1 is not allowed to be null.' error when converting latlng to GEOM Point
- SSIS conditional split, but want to check a pattern
- SQL Server query that add a column in a Select Query
- Using column name when using SqlDataReader.IsDBNull
- SQL Server Race Condition Question
- Trim and Replace functions fail to remove spacing with INTO statement
- An attempt to login using SQL authentication failed
- Using ui:repeat to output a ResultSet
- Lightweight SQL server for Raspberry Pi
- how to get the start and end dates of all weeks between two dates in SQL server?
- Updating an existing user does not work in php
- Stored procedure XML parsing performance
- SQL Contains() not returning results for 'The'
- Substract date from previous row by group SQL query
- Simple CTE recursive query
- Sql Select phone numbers from a many to many table with different types (mobile, home)
- Comparing text to varchar(max) parameter gives String or binary data would be truncated
- How to resolve ASP.NET DataContext connection error
- SQL Server filter rows which are being selected in other transactions
- Failed to update database because the database is read only
- using STRING_AGG and the LAST_VALUE function in same request
- Extract All Instances of String into Concatenated Result
- T-SQL Pivot with Custom Column names
- SQL Server query using Union - any good alternate possible?
- MS SQL/OPENQUERY call to Informix -- too many single quotes? too few?
- How to check which database exists and use it in the function?
- Performance difference between Primary Key and Unique Clustered Index in SQL Server
- Convert value in all rows to VARBINARY(50)
- SQL - What type of join is it and the best way to rewrite?
- Retrieving all descendants of an Id in SQL Server
- select same records multiple times with one column value changed in SQL
- How to select data based Group by ID,Year,Month in sqlserver?
- MS SQL - MySQL Migration in a legacy webapp
- Spring JDBC for SQL Server - Using SQLXML datatypes yields SQLServerException: Implicit conversion from data type xml to nvarchar(max) is not allowed
- Dynamic Column Name by Year
- SSIS Package stopped working with Error code: 0x80004005
- What should be the table contain updated row for update trigger
- SubQuery vs TempTable before Merge
- making a where clause optional by checking a variable value in a sql statement
- SELECT * FROM a subquery which beings with IF EXISTS