SQL Server – Updating a View on Multiple Joined Tables

sql serversql-server-2005t-sqlupdateview

Since MSDN does not say much, what happens exactly if I execute the following query?

update claims set status='Awaiting Auth.'
where status = 'Approved'

Can I use the ClaimStatusName column of the linked table dimClaimStatus to update the main table that is referenced via foreign-key?

The view itself queries multiple tables, the main table is tabData, which I also want to update with above query. I want to change fiClaimStatus in tabData from the FK that means Approved in the referenced table dimClaimStatus to Awaiting Auth.. Does it work this way?

Only one view row is possible for each row in tabData.

Here's the view:

CREATE VIEW [dbo].[Claims] 
AS 
  SELECT mu.MarketUnitName AS MarketUnit, 
         c.CountryName     AS Country, 
         gsp.GSPName       AS GSP, 
         gsp.WCMSKeyNumber AS GspNumber, 
         sl.SLName         AS SL, 
         sl.WCMSKeyNumber  AS SlNumber, 
         m.ModelName       AS Model, 
         m.SalesName       AS [Model-Salesname], 
         s.ClaimStatusName AS [Status], 
         d.Work_Order      AS [Work Order], 
         d.SSN_Number      AS IMEI, 
         .... more columns ....
         idData,         -- PK of main table tabData
         fiSL, 
         fiModel, 
         fiClaimStatus  -- FK to dimClaimStatus
  FROM   tabData AS d 
         INNER JOIN locSL AS sl 
                 ON d.fiSL = sl.idSL 
         INNER JOIN locGSP AS gsp 
                 ON sl.fiGSP = gsp.idGSP 
         INNER JOIN locCountry AS c 
                 ON gsp.fiCountry = c.idCountry 
         INNER JOIN locMarketUnit AS mu 
                 ON c.fiMarketUnit = mu.idMarketUnit 
         INNER JOIN modModel AS m 
                 ON d.fiModel = m.idModel 
         INNER JOIN dimClaimStatus AS s 
                 ON d.fiClaimStatus = s.idClaimStatus 
         INNER JOIN tdefProductType 
                 ON d.fiProductType = tdefProductType.idProductType 
         LEFT OUTER JOIN tdefServiceLevel 
                      ON d.fimaxServiceLevel = tdefServiceLevel.idServiceLevel 
         LEFT OUTER JOIN tdefActionCode AS ac 
                      ON d.fimaxActionCode = ac.idActionCode

Update

Since the table contains 20 million customer records, I wanted to know first what will happen before I execute it. After the comments and answers, I have executed it now. Immediate result was:

(1 row(s) affected)

Which is surprising, because there were several thousands of records in this status which seem to be updated now.

Update 2

Actually it didn't work as expected and the suspicious (1 row(s) affected) was right. Only the referenced table was updated. So now the status Approved changed to Awaiting Auth..

Conclusion:

It seems to be the best way to just avoid using a view to do updates. This works in my case:

UPDATE tabData 
SET fiClaimStatus = (SELECT idClaimStatus
                     FROM dimClaimStatus
                     WHERE ClaimStatusName = 'Awaiting auth.')
WHERE fiClaimStatus=(SELECT idClaimStatus
                     FROM dimClaimStatus
                     WHERE ClaimStatusName = 'Approved')

Best Answer

General view updatability

The key part of the CREATE VIEW (Transact-SQL) documentation is:

Generally, the Database Engine must be able to unambiguously trace modifications from the view definition to one base table.

Note that even if the view is technically updatable, it may not be actually updatable in practice, due to limitations of the query processor's reasoning. That is the subtlety behind the phrase, "...the Database Engine must be able to..."

The easiest way to be sure that a view is actually updatable is to request a pre-execution ("estimated") plan for the update query. If you get an error, either the view is not logically updatable, or the query processor can't tell that it is.

Requesting a "estimated" plan does not involve executing the query, naturally. The plan shown will also show you how much of the view definition the query optimizer was able to remove (because it is redundant). Typically, it does a good job with this, so the update view plan may look very similar to a plan for a simple update to the single affected base table.

Specific example

Can I use the ClaimStatusName column of the linked table dimClaimStatus to update the main table that is referenced via foreign-key? [...] I want to change fiClaimStatus in tabData.

Not using the query you posted:

update claims 
set status='Awaiting Auth.'
where status = 'Approved'

This changes the base table column associated with the view's exposed column name status. From the view definition, that is the alias for column ClaimStatusName in table dimClaimStatus.

The execution plan shows that dimClaimStatus is the table updated through the view:

Execution plan

If you want to update fiClaimStatus, that is the column you need to specify in the update statement. If that involves a lookup, chances are you can't use the view directly, as you originally wanted, but you could write something like:

update claims 
set fiClaimStatus =
(
    select CS.idClaimStatus
    from dbo.dimClaimStatus AS CS
    where CS.ClaimStatusName = 'Awaiting auth.'
)
where status = 'Approved';`
Related Question