Início / dba / Perguntas / 116615
Accepted
Rainbolt
Asked: 2015-10-01 07:38:21 +0800 CST

Como posso evitar a repetição da mesma condição em uma cláusula "quando correspondido, então atualize"?

  • 772

Eu quero mesclar uma tabela em outra. Preciso aplicar a lógica condicional na minha cláusula WHEN MATCHED, que idealmente seria feita assim:

MERGE INTO ATable AS a
USING BTable AS b
ON a.ID = b.ID
WHEN NOT MATCHED THEN
-- Do insert
WHEN MATCHED AND b.NeedsAdjustment = 1 THEN
UPDATE SET
    Col1 = b.Col1 + b.Adjustment
    ,Col2 = b.Col2 + b.Adjustment
    ,Col3 = b.Col3 + b.Adjustment
WHEN MATCHED THEN -- Default case (b.NeedsAdjustment <> 1)
UPDATE SET
    Col1 = b.Col1
    ,Col2 = b.Col2
    ,Col3 = b.Col3

Este não é um SQL válido. De acordo com a documentação do MSDN:

Se houver duas cláusulas WHEN MATCHED, uma deverá especificar uma ação UPDATE e a outra deverá especificar uma ação DELETE.

Isso me leva à seguinte consulta:

MERGE INTO ATable AS a
USING BTable AS b
ON a.ID = b.ID
WHEN NOT MATCHED THEN
-- Insert happens here
WHEN MATCHED THEN
UPDATE SET
    Col1 = CASE WHEN b.NeedsAdjustment = 1 THEN b.Col1 ELSE b.Col1 + b.Adjustment END
    ,Col2 = CASE WHEN b.NeedsAdjustment = 1 THEN b.Col2 ELSE b.Col2 + b.Adjustment END
    ,Col3 = CASE WHEN b.NeedsAdjustment = 1 THEN b.Col3 ELSE b.Col3 + b.Adjustment END

A lógica condicional é movida dentro da atualização para contornar o fato de que as mesclagens podem ter apenas uma WHEN MATCHED THEN UPDATEcláusula. Agora, em vez de uma verificação por linha, tenho uma verificação por linha por coluna (e há muito mais colunas do que as três do exemplo).

Posso evitar repetir essa condição para cada coluna que precisa ser atualizada? Existe uma maneira melhor de fazer atualizações condicionais que talvez não envolvam mesclagens?

sql-server update

4 respostas

  1. Eu recomendaria simplesmente evitar MERGEem primeiro lugar por todos os motivos listados aqui e simplesmente usar uma instrução de atualização padrão.

    UPDATE A 
SET
    Col1 = b.Col1 + b.Adjustment
    ,Col2 = b.Col2 + b.Adjustment
    ,Col3 = b.Col3 + b.Adjustment
FROM ATable as A
inner Join TableB as B
    ON a.ID = b.ID
    AND b.NeedsAdjustment = 1
;
UPDATE A 
SET
    Col1 = b.Col1 
    ,Col2 = b.Col2
    ,Col3 = b.Col3
FROM ATable as A
inner Join TableB as B
    ON a.ID = b.ID
AND b.NeedsAdjustment <> 1
--Or INSNUL(NeedsAdjustment,0) If it's a nullable column
;

    A longo prazo, essa seria uma abordagem mais estável do que usar MERGE. Eu também adicionei o <>porque, se não estiver lá, você UPDATEsubstituirá o primeiro arquivo UPDATE.

    • 7
  2. Best Answer

    MERGEé uma declaração útil, mas limitada em situações como esta. Sugiro simplesmente separar sua consulta para que você possa cobrir completamente sua lógica. Se você esperava ou exigia o fato de que a MERGEinstrução realizará suas operações em uma única transação, você pode declarar uma transação explícita em torno de sua lógica:

    BEGIN TRAN
INSERT
WHERE

UPDATE
WHERE

UPDATE
WHERE

COMMIT TRAN
    • 4

  3. Se NeedsAdjustmentfor uma bitcoluna ou se for uma coluna inteira que só pode ter valores 0 ou 1, você pode usar uma única instrução UPDATE como esta para cobrir os dois casos:

    UPDATE
  a
SET
  a.Col1 = b.Col1 + b.NeedsAdjustment * b.Adjustment,
  a.Col2 = b.Col2 + b.NeedsAdjustment * b.Adjustment,
  a.Col3 = b.Col3 + b.NeedsAdjustment * b.Adjustment
FROM
  dbo.ATable AS a
  INNER JOIN dbo.BTable AS b ON a.ID = b.ID
;

    Se a coluna for anulável, aplique adicionalmente ISNULL ou COALESCE:

    …
SET
  a.Col1 = b.Col1 + ISNULL(b.NeedsAdjustment, 0) * b.Adjustment,
  a.Col2 = b.Col2 + ISNULL(b.NeedsAdjustment, 0) * b.Adjustment,
  a.Col3 = b.Col3 + ISNULL(b.NeedsAdjustment, 0) * b.Adjustment
…
    • 4

  4. Usando um CTE, você pode "preparar" os dados que estão BTablepara poder usá-los uniformemente na instrução MERGE. Nesse caso, se uma linha não precisar ser ajustada, altere o Ajuste para zero.

    WITH b (Col1, Col2, Col3, Adjustment)
AS
(
    SELECT Col1, Col2, Col3, CASE WHEN NeedsAdjustment = 1 THEN Adjustment ELSE 0 END
    FROM BTable
)
MERGE INTO ATable AS a
USING b
    ON a.ID = b.ID
WHEN NOT MATCHED THEN
    -- Insert happens here
WHEN MATCHED THEN
    UPDATE SET
        Col1 = b.Col1 + b.Adjustment,
        Col2 = b.Col2 + b.Adjustment,
        Col3 = b.Col3 + b.Adjustment;
    • 4

relate perguntas