Updating a view on multiple joined tables

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

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:

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';`

StackExchange Database Administrators Q#127708, answer score: 9