How do I update multiple base table view?

Question

I got a view from multiple tables. All tables have a primary key column which are linked in a diagram as relationship. So I have no problem to update this view from a direct editing environment such as 'Enterprise Manager' or 'MS Access'. 

I wrote a stored procedure for UPDATE to use it from a client application (Visual Basic .NET) but when I executed this stored procedure from Query Analyzer I get error message :  

"View or function 'PartsFab' is not updatable because the modification affects multiple base tables"

But what I tried was changing just one column and actually it does not affect mutiple tables. Even if I change many columns which are from more than one table it shuold be able to update because there is no ambiguity. 

My question: How do I write a stored procedure which can update a multiple base table view? Or How do I change the design of this VIEW to be 'updatable by stored procedure'?  I think myView is already 'updatable' in the sense that there is no ambiguity in keys and can update it manually from direct table (in this case 'view') editing environment. 

Here I copy the SELECT statement for the VIEW 'PartsFab' and the stored procedure of UPDATE (failed with error message shown above):
------------------------------------------------------------------------------

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER  VIEW dbo.PartsFab
WITH   VIEW_METADATA
AS
SELECT     dbo.Parts.[Part Number], dbo.PartsFabInfo.Active, dbo.Parts.Type, dbo.Parts.Description AS [Part Name], dbo.PartsFabInfo.Material,
                      dbo.PartsImage.[Drawing File], dbo.PartsRemark.Remark, dbo.PartsFabInstruction.Instruction, dbo.PartsFabInfo.[Process 1],
                      dbo.PartsFabInfo.[Process 2], dbo.PartsFabInfo.[Process 3], dbo.PartsFabInfo.[Process 4], dbo.PartsFabInfo.[Process 5], dbo.PartsFabInfo.[Process 6],
                      dbo.PartsFabInfo.[Process 7], dbo.PartsFabInfo.[Process 8], dbo.PartsFabInfo.[Process 9], dbo.PartsFabInfo.[Process 10], dbo.Inventory.[Qty OnHand],
                      dbo.Inventory.[Date Last Checked], dbo.Inventory.[Qty Committed], dbo.Inventory.[Qty OnOrder], dbo.BlankSize.L1, dbo.BlankSize.W1,
                      dbo.BlankSize.N1, dbo.Shiplist1.Quantity AS SR, dbo.Shiplist2.Quantity AS RB, dbo.Shiplist3.Quantity AS EL,
                      dbo.Shiplist4.Quantity AS MV, dbo.Shiplist5.Quantity AS RN
FROM         dbo.Parts LEFT OUTER JOIN
                      dbo.PartsFabInfo ON dbo.Parts.[Part Number] = dbo.PartsFabInfo.[Part Number] LEFT OUTER JOIN
                      dbo.PartsFabInstruction ON dbo.Parts.[Part Number] = dbo.PartsFabInstruction.[Part Number] LEFT OUTER JOIN
                      dbo.PartsImage ON dbo.Parts.[Part Number] = dbo.PartsImage.[Part Number] LEFT OUTER JOIN
                      dbo.PartsRemark ON dbo.Parts.[Part Number] = dbo.PartsRemark.[Part Number] LEFT OUTER JOIN
                      dbo.Inventory ON dbo.Parts.[Part Number] = dbo.Inventory.[Part Number] LEFT OUTER JOIN
                      dbo.BlankSize ON dbo.Parts.[Part Number] = dbo.BlankSize.[Part Number] LEFT OUTER JOIN
                      dbo.Shiplist3 ON dbo.Parts.[Part Number] = dbo.Shiplist3.[Part Number] LEFT OUTER JOIN
                      dbo.Shiplist4 ON dbo.Parts.[Part Number] = dbo.Shiplist4.[Part Number] LEFT OUTER JOIN
                      dbo.Shiplist2 ON dbo.Parts.[Part Number] = dbo.Shiplist2.[Part Number] LEFT OUTER JOIN
                      dbo.Shiplist5 ON dbo.Parts.[Part Number] = dbo.Shiplist5.[Part Number] LEFT OUTER JOIN
                      dbo.Shiplist1 ON dbo.Parts.[Part Number] = dbo.Shiplist1.[Part Number]
WHERE     (dbo.Parts.[Part Number] NOT LIKE '#%')
WITH CHECK OPTION

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

------------------------------------------------------------------------------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER  PROCEDURE dbo.vs_PartsFabUpdateCommand
(
 @Part_Number nvarchar(11),
 @Active bit,
 @Type nvarchar(16),
 @Description nvarchar(255),
 @Material nvarchar(255),
 @DwgFile nvarchar(255),
 @Remark nvarchar(255),
 @Instruction nvarchar(255),
 @Process1 nvarchar(32),
 @Process2 nvarchar(32),
 @Process3 nvarchar(32),
 @Process4 nvarchar(32),
 @Process5 nvarchar(32),
 @Process6 nvarchar(32),
 @Process7 nvarchar(32),
 @Process8 nvarchar(32),
 @Process9 nvarchar(32),
 @Process10 nvarchar(32),
 @QtyOnHand int,
 @DateLastChecked datetime,
 @QtyCommitted int,
 @QtyOnOrder int
)
AS
SET NOCOUNT OFF;
UPDATE PartsFab
SET  Active=@Active,
 Type=@Type,
 [Part Name]=@Description,
 Material=@Material,
 [Drawing File]=@DwgFile,
 Remark=@Remark,
 Instruction=@Instruction,
 [Process 1]=@Process1,
 [Process 2]=@Process2,
 [Process 3]=@Process3,
 [Process 4]=@Process4,
 [Process 5]=@Process5,
 [Process 6]=@Process6,
 [Process 7]=@Process7,
 [Process 8]=@Process8,
 [Process 9]=@Process9,
 [Process 10]=@Process10,
 [Qty OnHand]=@QtyOnHand,
 [Date Last Checked]=@DateLastChecked,
 [Qty Committed]=@QtyCommitted,
 [Qty OnOrder]=@QtyOnOrder

WHERE ([Part Number] = @Part_Number);

SELECT * FROM PartsFab

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Answer 1

There is a strict guideline in order for the view to be updatable.
http://msdn.microsoft.com/library/en-us/createdb/cm_8_des_06_17zr.asp 

A view is considered an updatable partitioned view if:

• The view is a set of SELECT statements whose individual result sets are combined into one using the UNION ALL statement. Each individual SELECT statement references one SQL Server base table. The table can be either a local table or a linked table referenced using a four-part name, the OPENROWSET function, or the OPENDATASOURCE function (you cannot use an OPENDATASOURCE or OPENROWSET function that specifies a pass-through query).

Answer 2

But according to MS's online manual of SQL - 'CREATE VIEW' section, though the view is not a 'Partitioned' or 'INSTEAD OF trigger', still it could be 'updatable' as far as following three conditions are OK. And I think 'MyView' satisfies these three conditions:

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

If a view does not have INSTEAD OF triggers, or if it is not a partitioned view, then it is updatable only if the following conditions are satisfied:

• The select_statement has no aggregate functions in the select list and does not contain the TOP, GROUP BY, UNION (unless the view is a partitioned view as described later in this topic), or DISTINCT clauses. Aggregate functions can be used in a subquery in the FROM clause as long as the values returned by the functions are not modified. For more information, see Aggregate Functions.
  
  
• select_statement has no derived columns in the select list. Derived columns are result set columns formed by anything other than a simple column expression, such as using functions or addition or subtraction operators.
  
  
• The FROM clause in the select_statement references at least one table. select_statement must have more than non-tabular expressions, which are expressions not derived from a table. For example, this view is not updatable:

  CREATE VIEW NoTable AS SELECT GETDATE() AS CurrentDate, @@LANGUAGE AS CurrentLanguage, CURRENT_USER AS CurrentUser 

Answer 3

Yes, but you miss this paragraph...

<quote>
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.
</quote>

Long story short, the dml can only affect a single base table if the view is not a pv.  

Answer 4

Thanks oj,

Your comment is really helpful.

-------------------------------------------------------------------------------
"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. "
-------------------------------------------------------------------------------

I guess it is not a matter of whether 'MyView' is updatable or not but the correct syntax of the code. Definitely 'MyView' is 'updatable' in the sense that there is no ambiguity in the columns. What matters seems to be that an UPDATE statement in a stored procedure must refer a single table only at once. So maybe I should have written multiple UPDATE statements in the stored procedure.   


I am gonna try this Monday.

Answer 5

Just for curious people.

It works smoothly after I changed the stored procedure to UPDATE each table separately in myView. So I think a multiple table based VIEW is actually 'updatable' as far as there is no ambiguity in the columns for an UPDATE action.

Happy coding.

-----------------------------------------------------------------------------
-- My StoredProcedure for updating multi-table view

-- It was changed a little bit from real code for simplicity
-- Columns Part_Number,Alias1,Obsolete,Type,Description comes from a table
-- Column DwgFile comes from another table
-- Column Instruction comes from another table
-- Columns QtyOnHand, DateLastChecked comes from another table
-- All those tables have primary key column called 'Part Number'


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER    PROCEDURE dbo.vs_myViewUpdateCommand
(
 @Part_Number nvarchar(11),
 @Alias1 nvarchar(32),
 @Obsolete bit,
 @Type nvarchar(32),
 @Description nvarchar(255),
 @DwgFile nvarchar(255),
 @Instruction nvarchar(255),
 @QtyOnHand int,
 @DateLastChecked datetime
)
AS
SET NOCOUNT OFF;

UPDATE myView SET Alias1=@Alias1,Obsolete=@Obsolete,Type=@Type,[Description]=@Description WHERE ([Part Number] = @Part_Number);

UPDATE myView SET Instruction=@Instruction WHERE ([Part Number] = @Part_Number);

UPDATE myView SET [Drawing File]=@DwgFile WHERE ([Part Number] = @Part_Number);

UPDATE myView
SET  [Qty OnHand]=@QtyOnHand,
 [Date Last Checked]=@DateLastChecked
WHERE ([Part Number] = @Part_Number);

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-----------------------------------------------------------------------

Answer 6

Long story short, the dml can only affect a single base table if the view is not a pv .  

Hi,
What do the words/abbreviations I've rendered bold stand for? (dml & pv)
my guess is a contextual attempt, so pv=private and dml=database management/markup language

Answer 7

DML - data manipulation language

PV - I would guess it's parameterized view

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog