UPDATE statement on an INNER JOIN

Question

UPDATE statement on an INNER JOIN

michael.leach2015

SSCommitted

Points: 1597
More actions
February 24, 2020 at 5:42 am

#3728062
The following code gives these results:
```
SELECT *
FROM tblEmployee;

SELECT *
FROM tblDepartment;
```
I created a view on these 2 base tables.
```
CREATE VIEW VW_EmployeeDetail
AS
SELECT ID, Name, Gender, DepartmentID
FROM tblEmployee
INNER JOIN tblDepartment ON tblEmployee.DepartmentID = tblDepartment.DeptID
```
If can make an update to one of the base tables in the view as follows:
```
UPDATE VW_EmployeeDetail
SET Name = 'Kathy'          -- although this view consists of 2 base tables, I am updating only 1 table so this works just fine
WHERE ID = 5;
```
Since the view consists of an INNER JOIN, I figured I should be able to make the same update above by applying an UPDATE statement to an INNER JOIN (the same one I used in the view), because a view is just a saved SELECT statement, which in this case consists of an INNER JOIN.
So I tried this:
```
UPDATE VW_EmployeeDetail
SET Name = 'Kathy'          -- although this view consists of 2 base tables, I am updating only 1 table so this works just fine
WHERE ID = 5;
```
This resulted in an error: Incorrect syntax near the keyword 'FROM'.
I am just trying things out to get a better understanding of SQL. In short, I'm wondering if updating a view is essentially the same as updating a join? If there are differences, that would help me to better understand views and joins, and could help me to realize some things that I am overlooking.
- This topic was modified 4 years, 8 months ago by michael.leach2015.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 1

question, why update the view? - in the last example you only touch one table - keep it simple don't use unnecessary joins... even better - forget the view and put it in a stored procedure

MVDBA

Grant Fritchey SSC Guru Points: 398878 More actions · Answer 2

Updating a view isn't updating a join. In fact, I'm not sure how you update a join because you can't. Updating a view is using the fact that a view exposes tables to reference that view to access the underlying table. When we're talking about updates, the key is, don't think about updating groups of tables or sets of tables. Any update, insert, delete, is going to be one table at a time, no matter what. Yes, you can wrap all that in transactions so that it passes or fails together, but the activity is always one table at a time.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 3

Grant Fritchey wrote:

Updating a view isn't updating a join. In fact, I'm not sure how you update a join because you can't. Updating a view is using the fact that a view exposes tables to reference that view to access the underlying table. When we're talking about updates, the key is, don't think about updating groups of tables or sets of tables. Any update, insert, delete, is going to be one table at a time, no matter what. Yes, you can wrap all that in transactions so that it passes or fails together, but the activity is always one table at a time.

I've never been a fan of views - even worse, nested views (try to debug them) and then even worse..... nested views in access with linked servers.... sometimes I don't sleep at night thinking about them. 🙂

MVDBA

Jonathan AC Roberts SSCoach Points: 18340 More actions · Answer 4

I think there is a mistake in your question. The SQL you described that works is exactly the same as the SQL you say is giving you an error:

UPDATE VW_EmployeeDetail
SET Name = 'Kathy'          -- although this view consists of 2 base tables, I am updating only 1 table so this works just fine
WHERE ID = 5;

You should be able to update the view so long as the columns you are updating are only on 1 of the tables.

If you attempt to update columns on more than one of the tables you will get a 4405 error

not updatable because the modification affects multiple base tables

not an "incorrect syntax near the keyword 'FROM'" error:

Can you supply the DDL and SQL statement that is giving the "incorrect syntax near the keyword 'FROM'" error so we can repeat and see exactly what is wrong?

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 5

I've just done a quick scout on the issue (I never use update in join, so it's a nice refresher)

Microsoft SQL Server does not permit updating fields in multiple database tables used within a SQL view. According to Microsoft, “INSERT, UPDATE, and DELETE statements also must meet certain qualifications before they can reference a view that is updatable... UPDATE and INSERT statements can reference a view only if the view is updatable and the UPDATE or INSERT statement is written so that it modifies data in only one of the base tables referenced in the FROM clause of the view. A DELETE statement can reference an updatable view only if the view references exactly one table in its FROM clause."

my advice - ditch the view and update the single table

MVDBA

Johan Bijnens SSC Guru Points: 135364 More actions · Answer 6

and, depending on your view definition, the result must also meet the view specifications !

( With check option )

ref: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql?view=sql-server-ver15

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me