如何更新引用重复记录的表？

Question

我有两个SQL表。从另一个表中获取一个引用值，该表存储模块及其ID的列表，但这些描述并不是唯一的。我正在尝试删除表A的副本，但我不知道如何更新表B，使其仅引用单个值。

示例：

Table A:                                      Table B:

--------------------------------            ------------------------------------
ID      Description      RefID               ID            Name       
--------------------------------            ------------------------------------
1       Test 1           2                   1            QuickReports
--------------------------------            ------------------------------------
2       Test 2           1                   2            QuickReports
--------------------------------            ------------------------------------

我希望结果如下：

Table A:                                      Table B:

--------------------------------            ------------------------------------
ID      Description      RefID               ID            Name       
--------------------------------            ------------------------------------
1       Test 1           1                   1            QuickReports
--------------------------------            ------------------------------------
2       Test 2           1                  
--------------------------------        

我使用以下代码从表B中删除了副本，但未能更新表A中的记录。每个表都有500多条记录。

WITH cte AS(
    SELECT 
        Name,
    ROW_NUMBER() OVER (
        PARTITION BY
            Name
        ORDER BY 
            Name
        )row_num
    FROM ReportmodulesTest
)
    DELETE FROM cte
    WHERE row_num > 1;  

Answer 1

我找到了一个让这个过程更容易的解决方案。我首先使用Row_Number在表A中查找重复项，并使用SELECT INTO查找临时表。

SELECT
       a.Id
     , a.Name
     , ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Id DESC) RN
INTO
     #TestTable
FROM
     TableA a WITH(NOLOCK)

然后，我将JOIN表A和表B用于查看ID匹配的位置，并确定需要保留的ID以及需要删除的ID：

SELECT
       b.Id
     , b.Name
     , b.RefId
     , ToKeep.Id   KeepId
     , ToDelete.Id DeleteId
FROM
     #TestTable ToDelete
     JOIN TableB b WITH(NOLOCK)
        ON b.RefId = ToDelete.Id
     JOIN #TestTable ToKeep
        ON ToDelete.Name = ToKeep.Name
           AND ToKeep.RN = 1
WHERE ToDelete.RN > 1

然后使用类似的语句，我只需更新记录：

UPDATE b
SET
    b.RefId = ToKeep.Id,
FROM #TestTable ToDelete
     JOIN TableB b WITH(NOLOCK)
        ON b.RefId = ToDelete.Id
     JOIN #TestTable ToKeep
        ON ToDelete.Name = ToKeep.Name
           AND ToKeep.RN = 1
WHERE
      ToDelete.RN > 1

最后，我现在可以删除重复的记录：

DELETE a
FROM #TestTable b
     INNER JOIN TableA a
        ON b.Id = a.Id
WHERE
      b.RN > 1

在此之后，您可以使用相同的第一个SELECT语句来确保删除所有重复项。只需删除SELECT INTO语句即可。

感谢我的一位匿名同事提出了这个解决方案，并希望这能帮助到外面的人。

Answer 2

在从表B中删除之前，您需要先更新表A。

您将问题标记为MySQL，但该数据库不支持所显示的delete语句。我怀疑您正在运行SQL Server，下面是如何在该数据库中这样做：

update a
set refid = b.minid
from tablea
inner join (select name, id, min(id) over(partition by name) minid from tableb) b 
    on b.id = a.id and b.minid <> a.id

在MySQL中，您可以将相同的查询表述为：

update tablea a
from tablea
inner join (select name, id, min(id) over(partition by name) minid from tableb) b on b.id = a.id
set a.refid = b.minid
where b.minid <> a.id

Answer 3

您可以使用以下方法更新第一个表：

update a join
       (select b.*,
               min(id) over (partition by name) as min_id
        from b
       ) b
       on a.refid = b.id
    set a.refid = b.min_id
    where a.refid <> b.min_id;

然后，可以使用类似的逻辑删除第二个表中的行：

delete b
    from b join
         (select b.*,
                 min(id) over (partition by name) as min_id
          from b
         ) bb
         on bb.id = b.id
    where b.id <> bb.min_id;