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
tabData from the FK that means
Approved in the referenced table
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
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.
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
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')