The Hidden SQL Server Gem: UPDATE from SELECT
3 Maneiras simples de usar UPDATE from SELECT tornando o seu trabalho mais fácil
Você provavelmente já esteve nesta situação antes: Precisava de actualizar os dados numa tabela utilizando a informação armazenada noutra tabela. Frequentemente encontro pessoas que não ouviram falar da poderosa solução UPDATE from SELECT que o SQL Server fornece para este problema. De facto, lutei com este problema durante bastante tempo antes de descobrir esta gema.
Nas linhas seguintes, vou mostrar-lhe três truques que tornaram a minha vida mais simples em muitas ocasiões. Para isso, precisamos primeiro de duas tabelas:
Se quiserem seguir, podem obter o guião aqui: A Gema do Servidor SQL Escondida – UPDATE de SELECT.sql
Desenvolva a sua UPDATE a partir de um SELECT
Agora que temos o ambiente configurado, vamos mergulhar na forma de fazer isto funcionar. Antes de lhe mostrar a solução multi-mesas, deixe-me demonstrar a forma mais simples da sintaxe UPDATE FROM e mostrar-lhe um truque simples para tornar o desenvolvimento das suas declarações UPDATE realmente simples, escrevendo primeiro uma declaração SELECT e depois transformando-a numa actualização, apagando dois caracteres. Intrigado?
A tabela dbo.Orders contém e é_arquivada coluna. Esta é utilizada para arquivar encomendas com mais de 90 dias. No entanto, temos a regra adicional de que as encomendas que ainda não foram pagas ou enviadas não podem ser arquivadas. (Neste exemplo, a coluna is_arquivada não tem efeito físico. Veja o meu artigo SQL Server Pro Using Table Partitions to Archive Old Data in OLTP Environments se estiver interessado em como transformar a coluna is_archived numa verdadeira chave de arquivo.)
Uma declaração SELECT para devolver os registos arquivados ficaria assim:
FROM dbo.Orders AS O
WHERE O.order_date < DATEADD(DAY,-90,SYSDATETIME())
AND O.is_paid = 1
AND O.is_shipped = 1;
agora vamos adicionar-lhe um pouco de magia:
SELECT *, — */
is_archived = 1
FROM dbo.Orders AS O
WHERE O.order_date < DATEADD(DAY,-90,SYSDATETIME())
AND O.is_paid = 1
AND O.is_shipped = 1;
Esta é ainda uma simples declaração SELECT. Acabámos de acrescentar dois comentários e uma coluna adicional. Essa coluna adicional está a utilizar o nome de sintaxe invulgar do pseudónimo = valor com é equivalente ao valor mais comum da sintaxe do nome AS.
A beleza disto reside no facto de poder transformar esta selecção numa declaração UPDATE sintaticamente correcta, apenas removendo os dois traços em frente da palavra-chave UPDATE:
SELECT *, — */
is_archived = 1
FROM dbo.Orders AS O
WHERE O.order_date < DATEADD(DAY,-90,SYSDATETIME())
AND O.is_paid = 1
AND O.is_shipped = 1;
Esta actualiza a tabela dbo.Orders tal como e actualiza a declaração dbo.Orders SET… porque a declaração dbo.A tabela de ordens é pseudónimo de O e a UPDATE refere-se a esse mesmo O alias.
Adicionando um JOIN à declaração UPDATE
A questão que nos deixou aqui foi como podemos utilizar os dados de uma tabela para actualizar outra tabela. Não seria bom se pudéssemos simplesmente “JUNtar”? Boas notícias: A sintaxe acima permite-nos fazer exactamente isso.
Recentemente uma coluna order_count foi adicionada à tabela dbo.Clientes. O nosso trabalho agora é valorizar correctamente essa coluna com base nas encomendas reais feitas por cada cliente. Recomeçamos por escrever uma selecção primeiro:
div>–UPDATE C SET /*
SELECT *, — */
order_count = OA.cnt
FROM dbo.Customers AS C
JOIN(
SELECT O.customer_id,
COUNT(1) cnt
FROM dbo.Orders AS O
GROUP BY O.customer_id
br>)OA
ON C.customer_id = OA.customer_id;
Após a declaração SELECT devolver os resultados correctos, é fácil mudá-la para UPDATE:
SELECT *, — */
order_count = OA.cnt
FROM dbo.Customers AS C
JOIN(
SELECT O.customer_id,
COUNT(1) cnt
FROM dbo.Orders AS O
GROUP BY O.customer_id
)OA
ON C.customer_id = OA.customer_id;
Por causa da utilização do pseudónimo C, o SQL Server sabe actualizar o dbo.Tabela de clientes enquanto puxa a informação necessária de outra(s) tabela(s) referenciada(s) na declaração.
Usando UPDATE com um CTE
Se os CTEs são a sua coisa, pode mesmo ir um passo mais longe com isto. Desde que o SQL Server possa facilmente determinar o que pretende actualizar, pode realmente “ACTUALIZAR” um CTE directamente usando uma sintaxe muito semelhante:
(
SELECT O.customer_id,
COUNT(1) cnt
FROM dbo.Encomendas COMO O
GRUPO POR O.customer_id
),
cliente_ordem_contagens COMO O
(
SELECT
C.customer_id,
C.nome,
C.order_count,
OC.cnt new_order_cnt
FROM dbo. Clientes COMO C
JUNtar_contas_ordem COMO OC
ON C.customer_id = OC.customer_id
)
UPDATE COC SET /*
SELECT *, — */
order_count = COC.new_order_cnt
FROM customer_order_counts AS COC;
A única coisa que não pode fazer com nenhuma das afirmações acima, é actualizar os dados em duas tabelas ao mesmo tempo.
A Word of Caution
Esta sintaxe fornece-nos uma forma muito poderosa de escrever declarações UPDATE que requerem dados de mais do que uma tabela. No entanto, tenha cuidado para não escrever código que mostre comportamento aleatório. Dê uma olhada a esta declaração UPDATE:
Apenas como o SELECT destacado devolve um cliente com múltiplas encomendas múltiplas vezes, a UPDATE actualizaria de bom grado cada cliente várias vezes, substituindo cada vez a alteração anterior. Apenas a última alteração persistiria.
O problema com isso é que não há garantia de qual a ordem que acontece. A ordem depende do plano de execução escolhido e pode ser alterada a qualquer momento. Assim, embora a declaração acima possa resultar na redacção da data da última ordem nos seus testes, é provável que seja executada numa ordem diferente quando a declaração encontrar uma grande quantidade de dados. Isto levará a problemas difíceis de depurar, por isso preste atenção a esta possibilidade, ao escrever as suas declarações UPDATE FROM SELECT.