存在重复行时，如何用MERGE或SQL语句实现表间数据更新？

问题根源 #

MERGE报错的核心是目标表ChannelAdvisorProductLocation中存在重复的(InventoryNumber, WarehouseLocation)行，导致匹配逻辑命中多条记录，触发多匹配错误。以下是分步解决的方案，同时保留历史数据：

1. 清理目标表重复行（仅去重，保留历史记录） #

用CTE标记重复组，保留每组中的一条记录（可根据实际需求调整保留规则，比如最早创建的记录）：

WITH DuplicateRows AS (
    SELECT 
        InventoryNumber,
        WarehouseLocation,
        -- 按库存编号+仓库位置分组，给每行分配序号
        ROW_NUMBER() OVER (
            PARTITION BY InventoryNumber, WarehouseLocation 
            ORDER BY (SELECT NULL) -- 若无创建时间字段，用默认排序；有则替换为`CreatedDate ASC`保留最早记录
        ) AS RowSeq
    FROM ChannelAdvisorProductLocation
)
-- 删除序号大于1的重复行
DELETE FROM DuplicateRows WHERE RowSeq > 1;

2. 添加唯一约束（杜绝未来重复行） #

为避免后续同步再产生重复数据，给表添加唯一约束：

ALTER TABLE ChannelAdvisorProductLocation
ADD CONSTRAINT UC_Inventory_Location 
UNIQUE (InventoryNumber, WarehouseLocation);

3. 替换MERGE为分步骤更新+插入（稳定保留历史数据） #

原MERGE逻辑拆分为三步，避免多匹配报错的同时，完整保留历史位置记录：

步骤1：标记所有历史记录为非活跃

UPDATE ChannelAdvisorProductLocation 
SET Active = 0;

步骤2：更新与新数据匹配的记录为活跃状态

UPDATE target
SET target.Active = 1
FROM ChannelAdvisorProductLocation target
INNER JOIN (
    -- 从产品表拆分仓库位置，过滤源数据中的重复项
    SELECT 
        InventoryNumber, 
        TRIM(split.[value]) AS WarehouseLocation
    FROM ChannelAdvisorProduct p
    CROSS APPLY STRING_SPLIT(COALESCE(p.WarehouseLocation, 'NA'), ',') AS split
    GROUP BY InventoryNumber, split.[value]
    HAVING COUNT(*) = 1
) source 
    ON target.InventoryNumber = source.InventoryNumber 
    AND target.WarehouseLocation = source.WarehouseLocation;

步骤3：插入目标表中不存在的新位置记录

INSERT INTO ChannelAdvisorProductLocation (InventoryNumber, WarehouseLocation, Active)
SELECT 
    source.InventoryNumber, 
    source.WarehouseLocation, 
    1 -- 新插入记录直接标记为活跃
FROM (
    SELECT 
        InventoryNumber, 
        TRIM(split.[value]) AS WarehouseLocation
    FROM ChannelAdvisorProduct p
    CROSS APPLY STRING_SPLIT(COALESCE(p.WarehouseLocation, 'NA'), ',') AS split
    GROUP BY InventoryNumber, split.[value]
    HAVING COUNT(*) = 1
) source
LEFT JOIN ChannelAdvisorProductLocation target
    ON target.InventoryNumber = source.InventoryNumber 
    AND target.WarehouseLocation = source.WarehouseLocation
WHERE target.InventoryNumber IS NULL; -- 仅插入目标表无匹配的记录

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