主页 / dba / 问题 / 116615
Accepted
Rainbolt
Asked: 2015-10-01 07:38:21 +0800 CST

如何避免在“匹配时更新”子句中重复相同的条件？

  • 772

我想将一个表合并到另一个表中。我需要在我的 WHEN MATCHED 子句中应用条件逻辑，理想情况下可以这样完成：

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

这不是有效的 SQL。根据 MSDN 文档：

如果有两个 WHEN MATCHED 子句，则一个必须指定一个 UPDATE 操作，一个必须指定一个 DELETE 操作。

这导致我进行以下查询：

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

条件逻辑被移动到更新内部，以绕过合并只能有一个WHEN MATCHED THEN UPDATE子句的事实。现在，我不是每行检查一次，而是每行每列检查一次（并且列比示例中的三列多得多）。

我可以避免对需要更新的每一列重复这种情况吗？有没有更好的方法来进行可能不涉及合并的条件更新？

sql-server update

4 个回答

  1. 由于此处MERGE列出的所有原因，我建议首先避免使用，而只需使用标准更新语句。

    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
;

    从长远来看，这将是一种比使用MERGE. 我还添加了，<>因为如果那不存在，您UPDATE将覆盖第一个UPDATE.

    • 7
  2. Best Answer

    MERGE是一个方便的陈述，但在这种情况下受到限制。我建议简单地分解您的查询，以便您可以完全涵盖您的逻辑。如果您期望或要求该MERGE语句将在单个事务中执行其操作，您可以围绕您的逻辑声明一个显式事务：

    BEGIN TRAN
INSERT
WHERE

UPDATE
WHERE

UPDATE
WHERE

COMMIT TRAN
    • 4

  3. 如果NeedsAdjustment是bit列，或者如果它是只能具有值 0 或 1 的整数列，那么您可以使用这样的单个 UPDATE 语句来涵盖这两种情况：

    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
;

    如果该列可以为空，则另外应用 ISNULL 或 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. 使用 CTE，您可以“暂存”其中的数据，BTable以便您可以在 MERGE 语句中统一使用它。在这种情况下，如果一行不需要调整，请将调整更改为零。

    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

相关问题