如何在MERGE的WHEN NOT MATCHED BY Source条件中添加OR逻辑实现删除

你遇到的是SQL Server MERGE语句的语法限制：WHEN NOT MATCHED BY SOURCE子句无法直接添加额外过滤条件（比如判断Target.DisplayCustNumber为空或NULL）。要同时实现两种删除场景，这里有两种可靠的解决方案：

方案一：拆分操作（推荐，直观易维护） #

既然MERGE的删除分支不能同时处理两种条件，我们可以把操作拆到同一个事务中完成，保证数据一致性：

1. 先用MERGE处理更新、插入，以及删除源数据中不存在的目标记录；
2. 再单独执行DELETE语句清理目标表中DisplayCustNumber为空或NULL的记录。

完整代码如下：

BEGIN TRANSACTION;

-- 第一步：处理更新、插入，以及删除源中不存在的目标记录
MERGE dbo.SystemCustomer AS TARGET
USING (
    SELECT * 
    FROM dbo.vClients 
    WHERE ClientNumber IS NOT NULL
) AS SOURCE
ON TARGET.CustNumber = SOURCE.ClientNumber
WHEN MATCHED AND (
    TARGET.DisplayCustNumber <> SOURCE.VisualID 
    OR TARGET.CustName <> ISNULL(SOURCE.CustName, '')
) 
THEN UPDATE SET 
    TARGET.DisplayCustNumber = SOURCE.VisualID,
    TARGET.CustName = ISNULL(SOURCE.CustName, '')
WHEN NOT MATCHED BY TARGET THEN
    INSERT (CustNumber, DisplayCustNumber, CustName)
    VALUES (SOURCE.ClientNumber, SOURCE.VisualID, ISNULL(SOURCE.CustName, ''))
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

-- 第二步：删除DisplayCustNumber为空或NULL的记录
DELETE FROM dbo.SystemCustomer
WHERE ISNULL(DisplayCustNumber, '') = '';

COMMIT TRANSACTION;

为什么用事务？ 确保两步操作要么全部成功，要么全部回滚，避免出现数据不一致的情况。

方案二：扩展SOURCE数据集（单MERGE语句实现） #

如果一定要用单条MERGE完成所有操作，可以扩展SOURCE数据集，把需要删除的DisplayCustNumber为空的目标记录也包含进去，通过WHEN MATCHED分支触发删除：

MERGE dbo.SystemCustomer AS TARGET
USING (
    -- 原有效客户端数据
    SELECT 
        ClientNumber,
        VisualID,
        CustName,
        'VALID' AS RecordType
    FROM dbo.vClients 
    WHERE ClientNumber IS NOT NULL

    UNION ALL

    -- 虚拟数据：标记需要删除的目标记录（DisplayCustNumber为空）
    SELECT 
        CustNumber AS ClientNumber,
        'DELETE_FLAG' AS VisualID,
        '' AS CustName,
        'DELETE' AS RecordType
    FROM dbo.SystemCustomer
    WHERE ISNULL(DisplayCustNumber, '') = ''
) AS SOURCE
ON TARGET.CustNumber = SOURCE.ClientNumber
WHEN MATCHED AND SOURCE.RecordType = 'VALID' AND (
    TARGET.DisplayCustNumber <> SOURCE.VisualID 
    OR TARGET.CustName <> ISNULL(SOURCE.CustName, '')
) THEN UPDATE SET 
    TARGET.DisplayCustNumber = SOURCE.VisualID,
    TARGET.CustName = ISNULL(SOURCE.CustName, '')
WHEN MATCHED AND SOURCE.RecordType = 'DELETE' THEN
    DELETE
WHEN NOT MATCHED BY TARGET AND SOURCE.RecordType = 'VALID' THEN
    INSERT (CustNumber, DisplayCustNumber, CustName)
    VALUES (SOURCE.ClientNumber, SOURCE.VisualID, ISNULL(SOURCE.CustName, ''))
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

注意：这种方式逻辑相对复杂，要确保真实数据的VisualID不会和虚拟标记（比如'DELETE_FLAG'）冲突，后期维护成本较高。

额外优化提示 #

你原语句中的IsNull(SOURCE.VisualID,'') <> '' And IsNull(TARGET.DisplayCustNumber,'') <> ''属于冗余判断，因为当字段为空时，TARGET.DisplayCustNumber <> SOURCE.VisualID已经会触发更新（如果业务需要严格区分空值和非空值的匹配，可以保留原判断）。

内容的提问来源于stack exchange，提问作者IZ4