Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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')
share|improve this question
    
Side question, couldnt you have wrapped this inside of a transaction and rollback if what happened didn't meet your needs? – JonH Jan 29 at 14:38
    
@JonH: yes, i could have used a transaction. But even then it would not have been clear immediately that it hasn't worked as desired. Because a quick SELECT * FROM CLAIMS WHERE STATUS = 'Approved' after the update returned an empty resultset. That would also be the case if the foreign-key would have been updated instead of the reference table's ClaimStatusName. – Tim Schmelter Jan 29 at 14:56
up vote 7 down vote accepted

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';`
share|improve this answer
    
Many thanks, using the estimated execution plan is a good idea which i will follow in future. Here is it. – Tim Schmelter Jan 29 at 12:52

Imagine your UPDATE is updating s with the FROM clause of your view. Then read this blog I wrote recently to see how it could be affected.

http://sqlblog.com/blogs/rob_farley/archive/2016/01/12/join-effects-with-update.aspx

Assuming you're not breaking the rules for updateable views, then you should be okay. Just also be wary of the things in my post.

share|improve this answer
    
I will have a deeper look after work. Thanks for sharing this. – Tim Schmelter Jan 29 at 12:51
1  
Instead of linking to your blog, can you at least post the relevant snippets here. Blogs tend to disappear and reappear during ones lifetime. – JonH Jan 29 at 14:37
    
Agreed. But I had limited time before going to sleep, and thought writing what I did would be better than saying nothing. – Rob Farley Jan 29 at 18:29

You update column state inside the view. It refferences s.ClaimStatusName AS [Status] inside the view. From the code of the view we see that you update dimClaimStatus table (dimClaimStatus AS s).

Seeing that you have 2 columns idClaimStatus and ClaimStatusName you have ID \ Name structure of the table. Inside the view you show ClaimStatusName.

So you update name inside dimClaimStatus table. This is the reason for (1 row(s) affected).

When you refresh the view you see new string inside multiple rows inside the view.

share|improve this answer
1  
The next thing that made me believe that the update has worked was that SELECT * FROM CLAIMS WHERE STATUS = 'Approved' didn't yield any rows after the update. But actually it didn't return records because the referenced table has changed by the update. So not using a view for updates seems to be the safest approach. – Tim Schmelter Jan 29 at 13:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.