Блог

У меня есть две таблицы SQL. Каждый получает ссылочное значение из другой таблицы, в которой хранится список модулей и их идентификатор. Но эти описания не уникальны. Я пытаюсь удалить дубликаты таблицы 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;  
 

Вам нужно будет сначала обновить таблицу A, прежде чем удалять из таблицы B.

Вы отметили свой вопрос 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
 

Вы можете обновить первую таблицу с помощью :

 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;
 

Я нашел решение, которое упростило этот процесс. Сначала я использую 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, чтобы увидеть, где совпадают идентификаторы, и определить, какой идентификатор мне нужно сохранить, а какие идентификаторы мне нужно удалить:

 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 инструкцию.

Спасибо моему анонимному коллеге за это решение и надеюсь, что это кому-то поможет.