how do i update multiple tables ina view 2

[janwills]

I am unable to update a view in sqlserver. I get the following message.



Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]View or function 'dbo.VIEW_Milestones' is not updatable because the
modification affects multiple base tables

Help.....

 

[SQLSister]

You write a stored procedure with multiple update statements to update the fields for each table, one table at a time. Make sure you put them in the correct order or you may have problems with the updates. make sure to enclose all the updates in one transaction so they can all be rolled back if there is a problem with one.

 

[MeanGreen]

I was able to update through the view provided you have a way to uniquely identify the data. Here is an example:


create table test1
(idno integer identity(1,1)
, column2 varchar(25),
column3 varchar(25))

create table test2
(idno integer,
description varchar(35))

create view vw_test
as
select t1.idno,column2,column3,description
from test1 t1 join test2 t2
on t1.idno = t2.idno

insert test1 values ('hello','world')
insert test2 values (1,'basic')

select * from vw_test

update vw_test
set description = 'world basic'
where idno = 1

select * from vw_test


This does work. Hope this helps.

 

[MeanGreen]

And this is from BOL:

Modifying Data Through a View
You can modify data through a view in these ways:

Use INSTEAD OF triggers with logic to support INSERT, UPDATE and DELETE statements.


Use updatable partitioned views that modify one or more member tables.
If a view does not use an INSTEAD OF trigger or is not an updatable partitioned view, it can still be updatable provided that:

If a view does not use an INSTEAD OF trigger or is not an updatable partitioned view, it can still be updatable provided that:

The view contains at least one table in the FROM clause of the view definition; the view cannot be based solely on an expression.


No aggregate functions (AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, VARP) or GROUP BY, UNION, DISTINCT, or TOP clauses are used in the select list. However, aggregate functions can be used within a subquery defined in the FROM clause provided that the derived values generated by the aggregate functions are not modified.


Note Partitioned views using the UNION ALL operator can be updatable.

No derived columns are used in the select list. Derived columns are result set columns formed by anything other than a simple column reference.
Guidelines for Modifying Data Through a View
Before you modify data through a view without using an INSTEAD OF trigger or an updatable partitioned view, consider these guidelines:

All data modification statements executed against the view must adhere to the criteria set within the SELECT statement defining the view if the WITH CHECK OPTION clause is used in the definition of the view. If the WITH CHECK OPTION clause is used, rows cannot be modified in a way that causes them to disappear from the view. Any modification that would cause this to happen is canceled and an error is displayed.


SQL Server must be able to resolve unambiguously the modification operation to specific rows in one of the base tables referenced by the view. You cannot use data modification statements on more than one underlying table in a single statement. Therefore, the columns listed in the UPDATE or INSERT statement must belong to a single base table within the view definition.


All the columns in the underlying table that are being updated and do not allow null values have values specified in either the INSERT statement or DEFAULT definitions. This ensures that all the columns in the underlying table that require values have them.


The data modified in the columns in the underlying table must adhere to the restrictions on those columns, such as nullability, constraints, DEFAULT definitions and so on. For example, if a row is deleted, all the underlying FOREIGN KEY constraints in related tables must still be satisfied for the delete to succeed.


A distributed partition view (remote view) cannot be updated using a keyset-driven cursor. This restriction can be resolved by declaring the cursor on the underlying tables and not on the view itself.
Additionally, to delete data in a view:

Only one table can be listed in the FROM clause of the view definition.
The READTEXT and WRITETEXT statements cannot be used with text, ntext, or image columns in a view.



Hope this helps.

 

[tlbroadbent]

Meangreen,

Your first example works if only one column is updated. However, if you attempt to update columns in the two tables via the view SQL Server will raise an error which is the original problem noted by janwills.

Example:

update vw_test
set description = 'world basic', --column in test2 table
Column3='New value' --column in test1 table
where idno = 1

Result:

Server: Msg 4405, Level 16, State 2, Line 1
View or function 'vw_test' is not updatable because the modification affects multiple base tables.

---------------------------------------------------

I recommend posting a link to lengthy references rather than posting the entire article. Posting lengthy excerpts from Microsoft's documents may violate the copyright. Alternately, you can extract and post the high points or provide a reference such as the article title. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.