A proper understanding of Views in SQL Server will definitely help you become a better database developer. Views allow us to basically save a query as an object in the database, and make it very easy to run the query with simple commands. But what if we want to make an updatable View?
That is, a View we can both query and perform INSERT/UPDATE statements to.
Normally, when we think of a View, we think of a simple SELECT statement against the View which runs the underlying query in the definition of the View. But sometimes, maybe we want to modify the data returned by a View via an UPDATE statement. It that sense, we’re saying we want to update data in the underlying table(s) defined in the View.
In this tutorial, we’ll learn what it means for a View to be considered updatable.
If you need a quick rundown on what a View is in SQL Server, you can check out this tutorial:
What is a View in SQL Server? A Guide for Beginners
Also, the following FREE Ebook will definitely help you:
FREE Ebook on SQL Server Table Expressions!
This Ebook provides an excellent summary of everything you need to know about the different table expressions we can create in Microsoft SQL Server, including Views. Everything discussed in this tutorial can be found in the FREE Ebook. It will be a great resource for you to reference throughout your career as a data professional. Make sure you download it today!
We will discuss the following topics in this tutorial:
- The idea behind an updatable View
- An UPDATE statement against a View can only effect one target table at a time
- Your UPDATE statement cannot update data in a derived column
- The handy WITH CHECK OPTION
- Tips and tricks
Let’s do it
1. The idea behind an updatable View
There really isn’t anything special you need to do to a View to make it updatable. Instead, there are simply some rules you need to be aware of when running INSERT, UPDATE, or DELETE operations against a View. This tutorial is a discussion about those rules you need to know.
We’ll assume you have permission in SQL Server to perform DML statements to a View. There are definitely ways to restrict the kinds of operations that can be performed on objects in SQL Server. That is something your database administrator would control. But again, we’ll assume this is your database and you have complete control of it.
Let’s create some data to work with. Say we have the following Customers table:
CREATE TABLE Customers ( CustID INT, FirstName VARCHAR(20), LastName VARCHAR(20) ) INSERT INTO Customers (CustID, FirstName, LastName) VALUES (1, 'Joshua','Porter'), (2, 'Andrew','Bluefield'), (3, 'Jack','Cloverfield'), (4, 'Ronald','Glover')
Then we have a corresponding CustomerContact table. This table simply stores the contact info for each customer. The two tables are linked by the CustID values:
CREATE TABLE CustomerContact ( CustID INT, Phone VARCHAR(12), Email VARCHAR(25), Country VARCHAR(3) ) INSERT INTO CustomerContact (CustID, Phone, Email, Country) VALUES (1, '834-736-8832', 'jPorter@gmail.com', 'USA'), (2, '577-213-6543', 'aBluefield@gmail.com', 'UK'), (3, '405-872-2873', 'jCloverfield@gmail.com', 'USA'), (4, '225-982-9833', 'rGlover@gmail.com', 'CA')
Cool, here is a simple SELECT statement to show you what your data should look like:
Great. Let’s create a very simple View called CustomerDetails to show us all the details we have for each customer. This will, of course, involve an INNER JOIN between the two tables:
CREATE VIEW CustomerDetails AS SELECT Cust.CustID, Cust.FirstName, Cust.LastName, Cont.Phone, Cont.Email, Cont.Country FROM Customers as Cust INNER JOIN CustomerContact AS Cont ON Cust.CustID = Cont.CustID
Let’s do a quick SELECT statement against the View to see what it looks like:
Cool, let’s start looking at some of those rules you should know.
2. An UPDATE statement against a View can only effect one target table at a time
When it comes to UPDATE statements (and also INSERT statements), we can only effect one target table at a time.
Let’s just show you an example. Let’s say we want to update the first row in our View. This is the row for our customer Joshua Porter. Let’s say we want to update his LastName and Email address.
Here is what the statement would look like:
UPDATE CustomerDetails SET LastName = 'Potter', Email = 'jPotter@gmail.com' WHERE CustID = 1
So remember, the LastName column is from the Customers table, while the Email column is from the CustomerContact table.
This UPDATE statement violates the rule!
In a single UPDATE statement, we’re trying to update columns from multiple tables. If we try to run this UPDATE statement, we get an error message.
The error says: View or function ‘CustomerDetails’ is not updatable because the modification affects multiple base tables.
Notice you need to know a thing or two about the underlying tables in the View before you can attempt a DML statement.
If we wanted to update our data, we would need to perform two UPDATE statements against the View:
UPDATE CustomerDetails SET LastName = 'Potter' WHERE CustID = 1 UPDATE CustomerDetails SET Email = 'jPotter@gmail.com' WHERE CustID = 1
Each UPDATE statement is targeting only one table at a time, so we’re good:
The same is true for INSERT statements
I said earlier that the same story is true for INSERT statements, too. This INSERT statement fails:
Again, our INSERT statement specifies columns from both underlying table. The rule is it can only specify values from one table at a time. We would also need to split this into two INSERT statements.
In this case, it would just be easier to perform the INSERT statements to the underlying tables directly, and NOT to the View. This is simply because each table has it’s own CustID column. For example, take a look at the error I get when I try to insert into the View, specifying columns from only the CustomerContact table:
I think SQL Server is seeing the CustID column and thinks we want to update the Customers.CustID column, but we don’t. I tried explicitly saying “CustomerContact.CustID“, but it still didn’t work.
So again, in this case, we’re just going to insert into the underlying tables directly:
Done.
Think outside the box
If we didn’t want to type two statements every time we wanted to do an UPDATE or an INSERT, we could think about using some of the tools SQL Server has available to us. The first thing that comes to mind is a trigger.
We can create a trigger on the CustomerContact View that fires when someone attempts to perform an UPDATE or INSERT against the View. In the definition of the trigger, we would have code to do the two separate UPDATE or INSERT statements for us.
This would be called an “instead of” trigger. The idea is that when SQL Server see’s that we’re trying to do an UPDATE or INSERT against the View, it will instead run the code in the definition of the trigger, which as I said would likely split the work into two statements.
I’m also thinking one could write a stored procedure to do the UPDATEs or INSERTs for us. Rather than attempt an UPDATE or INSERT against the View, you could just execute a stored procedure to do the work. Again, in that stored procedure, you would likely have code to split the work into two statements.
But I’m just split-balling here. Use your imagination!
3. Your UPDATE statement cannot update data in a derived column
In the underlying query within your View, you can definitely have one or more columns that are derived.
Let’s modify our View to contain a derived column called Full Name:
ALTER VIEW CustomerDetails AS SELECT Cust.CustID, CONCAT(Cust.FirstName, ' ', Cust.LastName) AS 'Full Name', Cont.Phone, Cont.Email, Cont.Country FROM Customers as Cust INNER JOIN CustomerContact AS Cont ON Cust.CustID = Cont.CustID
Our new Full Name column is just the FirstName and LastName columns combined together (with a space in between).
Here are the results of the View now:
The new Full Name column is a derived column, which means we cannot run an UPDATE against the View to change this particular column value:
The message we get is: Update or insert of view or function ‘CustomerDetails’ failed because it contains a derived or constant field.
Sure, you and I know the Full Name is composed of columns from just one table, but SQL Server isn’t smart enough to know we want ‘Joshua’ to be the FirstName and ‘Preacher’ to be the LastName.
So just remember that you cannot update derived columns in a View.
A more simple derived column still cannot be updated
To drive the point home, let’s look at an even easier derived column. Let’s just change the Email column to return values in upper case:
ALTER VIEW CustomerDetails AS SELECT Cust.CustID, CONCAT(Cust.FirstName, ' ', Cust.LastName) AS 'Full Name', Cont.Phone, UPPER(Cont.Email) AS 'Email', Cont.Country FROM Customers as Cust INNER JOIN CustomerContact AS Cont ON Cust.CustID = Cont.CustID
Here’s the data:
Ok, so the new Email column is technically a derived column now. This means we can’t update values in that column.
Let’s try it:
Yep, still fails! It doesn’t matter how simple our derived column is. At the end of the day, SQL Server still won’t let you update values in a derived column of a View!
4. The handy WITH CHECK OPTION
This last topic is something you should be aware of if you plan on inserting or updating data through Views.
Let’s create a separate View called USACustomerContactInfo to show us contact details for customers who live only in ‘USA‘:
CREATE VIEW USACustomerContactInfo AS SELECT CustID, Phone, Email, Country FROM CustomerContact WHERE Country = 'USA'
Notice the WHERE clause to apply a filter to our results. Let’s see what the View returns:
Ok, so it appears to be working correctly.
Let’s perform an UPDATE statement against the View to change CustID # 5 to have a Country of ‘CA‘ for Canada:
We understand why we don’t see CustID 5 anymore, right?
They no longer have a Country code of ‘USA‘, which is what our View filters on. Therefore, they are excluded from the result set of the View.
But if I’m a novice, I might think I just accidentally deleted CustID 5. They aren’t in my results anymore, so “what have I done”?!!!
There is a simple way to prevent users from modifying data to where it would violate the filters of the View. In other words, we can add a setting to prevent users from changing a row from one they can see to a row they cannot see.
It’s done through the WITH CHECK OPTION setting. We would add those keywords to the very end of the View, like this:
ALTER VIEW USACustomerContactInfo AS SELECT CustID, Phone, Email, Country FROM CustomerContact WHERE Country = 'USA' WITH CHECK OPTION
So if we run that ALTER VIEW statement, then try to change one of our ‘USA‘ customers again, we see we can’t do it:
Here’s that full error message:
The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.
It’s a very useful tool to know. Keep it in mind!
5. Tips and tricks
Here is a list of a few tips and tricks you should know when working with updatable views:
Tip #1: Updating the underlying tables is more ideal
You probably gathered how things can get weird when trying to perform UPDATE or INSERT operations against a View. In my opinion, it’s easier and safer to just modify the data in the underlying tables instead.
If done this way, you don’t need to worry about which columns belong to which table.
In my opinion, Views aren’t really meant to have UPDATE, INSERT or DELETE statements ran against them very often. They’re meant to run with SELECT statements to gather useful information through simple commands.
So, if you can, resist to urge to perform your updates against the View. Instead, modify the underlying tables instead.
Tip #2: There isn’t something special you need to do to your View to make it updatable
Remember, there isn’t a special setting to make your View updatable. Instead, there are a few simple rules you need to understand about performing DML statements against a View:
- We can only update columns from one underlying table at a time
- We cannot update data in derived columns
- The WITH CHECK OPTION setting is useful for data integrity
Next Steps:
Leave a comment if you found this tutorial helpful!
Don’t forget to download your FREE Ebook:
FREE Ebook on SQL Server Table Expressions!
This Ebook provides an excellent summary of everything you need to know about the different table expressions we can create in Microsoft SQL Server, including Views. It will be a great resource for you to reference throughout your career as a data professional. Make sure you download it today!
If you missed my introduction tutorial on Views, you should definitely check it out. In that tutorial, I cover everything else you need to know about Views in SQL Server and how they can help you become a better database developer and administrator:
What is a View in SQL Server? A Guide for Beginners
Also, Views are part of the Top 6 tools you should know for writing AWESOME Select statements. Make sure you read that tutorial to learn all about the other basic querying tools you should know if you want to master database queries!
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, or if you are struggling with a different topic related to SQL Server, I’d be happy to discuss it. Leave a comment or visit my contact page and send me an email!
