Como o MSDN não diz muito, o que acontece exatamente se eu executar a seguinte consulta?
update claims set status='Awaiting Auth.'
where status = 'Approved'
Posso usar a ClaimStatusName
coluna da tabela vinculada dimClaimStatus
para atualizar a tabela principal referenciada por chave estrangeira?
A visualização em si consulta várias tabelas, a tabela principal é tabData
, que também desejo atualizar com a consulta acima. Eu quero mudar fiClaimStatus
do tabData
FK que significa Approved
na tabela referenciada dimClaimStatus
para Awaiting Auth.
. Funciona assim?
Apenas uma linha de exibição é possível para cada linha em tabData.
Aqui está a vista:
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
Atualizar
Como a tabela contém 20 milhões de registros de clientes, queria saber primeiro o que vai acontecer antes de executá-la. Após os comentários e respostas, eu o executei agora. O resultado imediato foi:
(1 row(s) affected)
O que é surpreendente, porque havia vários milhares de registros neste status que parecem estar atualizados agora.
Atualização 2
Na verdade não funcionou como esperado e o suspeito (1 row(s) affected)
estava certo. Apenas a tabela referenciada foi atualizada. Então agora o status Approved
mudou para Awaiting Auth.
.
Conclusão:
Parece ser a melhor maneira de evitar o uso de uma exibição para fazer atualizações. Isso funciona no meu caso:
UPDATE tabData
SET fiClaimStatus = (SELECT idClaimStatus
FROM dimClaimStatus
WHERE ClaimStatusName = 'Awaiting auth.')
WHERE fiClaimStatus=(SELECT idClaimStatus
FROM dimClaimStatus
WHERE ClaimStatusName = 'Approved')
Capacidade de atualização da visão geral
A parte chave da
CREATE VIEW (Transact-SQL)
documentação é:Observe que mesmo que a visão seja tecnicamente atualizável, ela pode não ser realmente atualizável na prática, devido a limitações de raciocínio do processador de consultas. Essa é a sutileza por trás da frase "...o Mecanismo de Banco de Dados deve ser capaz de..."
A maneira mais fácil de garantir que uma exibição seja realmente atualizável é solicitar um plano de pré-execução ("estimado") para a consulta de atualização. Se você receber um erro, a exibição não é logicamente atualizável ou o processador de consulta não pode dizer que é.
Solicitar um plano "estimado" não envolve a execução da consulta, naturalmente. O plano mostrado também mostrará quanto da definição de exibição o otimizador de consulta foi capaz de remover (porque é redundante). Normalmente, ele faz um bom trabalho com isso, portanto, o plano de exibição de atualização pode parecer muito semelhante a um plano para uma atualização simples na única tabela base afetada.
Exemplo específico
Não usando a consulta que você postou:
Isso altera a coluna da tabela base associada ao status do nome da coluna exposta da exibição . Na definição da exibição, esse é o alias da coluna ClaimStatusName na tabela dimClaimStatus .
O plano de execução mostra que dimClaimStatus é a tabela atualizada através da view:
Se você deseja atualizar
fiClaimStatus
, essa é a coluna que você precisa especificar na instrução de atualização. Se isso envolver uma pesquisa, é provável que você não possa usar a exibição diretamente, como queria originalmente, mas poderia escrever algo como:Imagine que seu UPDATE está atualizando
s
com a cláusula FROM da sua view. Então leia este blog que escrevi recentemente para ver como isso pode ser afetado.http://blogs.lobsterpot.com.au/2016/01/12/join-effects-with-update/
Supondo que você não esteja quebrando as regras para exibições atualizáveis , você deve estar bem. Apenas tenha cuidado com as coisas no meu post.
Você atualiza a coluna
state
dentro do arquivoview
. Ele faz referências.ClaimStatusName AS [Status]
dentro da exibição. A partir do código doview
vemos que você atualiza adimClaimStatus
tabela (dimClaimStatus AS s
).Vendo que você tem 2 colunas
idClaimStatus
eClaimStatusName
tem a estrutura ID\Name da tabela. Dentro da exibição que você mostraClaimStatusName
.Então você atualiza o nome dentro da
dimClaimStatus
tabela. Esta é a razão de(1 row(s) affected)
.Quando você atualiza a exibição, vê uma nova string dentro de várias linhas dentro da exibição.