Поиск наименьших идентификаторов дубликатов и обновление таблиц в соответствии с этими идентификаторами

Эта проблема

У меня есть база данных SQL с таблицей для хэштегов, многие из которых являются дубликатами в отношении их имен.

Заявление как

SELECT     *  
FROM       HashTag  
ORDER BY   Name

возвращает что-то вроде

Id   |  Name
1947 |  test
1950 |  sample
1962 |  test
1963 |  sample
1986 |  test
2014 |  example

Я хочу сохранить только хэш-тег с самым низким Id для каждого Имени (1947 для "теста" и 1950 для "образца") и обновить другие таблицы с этим Id, заменив более высокие идентификаторы (пример: обновление хэш-тега "test"; самый низкий Id = 1947, более высокие идентификаторы = 1962, 1986). Эти SQL-операторы обновляются вручную на данный момент и будут выглядеть следующим образом:

UPDATE             HashTaggedActivity
SET [HashTag_id]   = 1947
WHERE HashTag_id   in (1962, 1986)

Update             HashTaggedGroup
SET [HashTag_id]   = 1947
WHERE HashTag_id   in (1962, 1986)

DELETE             ht
FROM               HashTag ht
WHERE              ht.Id in (1962, 1986)

После этого я должен сделать это для HashTag 'sample', который подвержен ошибкам и утомителен. "Пример" HashTag не является дубликатом и не должен приводить к обновлению других таблиц.

Есть ли способ написать SQL-оператор для этого для каждого случая повторяющихся имен в таблице HashTag?

Что я пробовал до сих пор

Я думаю, что я должен объединить заявление для получения дубликатов по заказу Id

select ht.Id, ht.Name, htc.dupeCount
from HashTag ht
inner join (
    SELECT ht.Name, COUNT(*) AS dupeCount
    FROM HashTag ht
    GROUP BY ht.Name
    HAVING COUNT(*) > 1
) htc on ht.Name = htc.Name
ORDER BY Id

который дает

Id   |  Name    | dupeCount
1947 |  test    | 3
1950 |  sample  | 2
1962 |  test    | 3
1963 |  sample  | 2
1986 |  test    | 3
2014 |  example | 1

с моими инструкциями UPDATE и DELETE в соответствии с dupeCount, но я не уверен, как это сделать;-)

Спасибо заранее и наилучшими пожеланиями,

Майкл

Источник

2 ответа

Решение

Первые два оператора обновления сначала получают имя на основе hashtag_id (самый внутренний выбор), затем получают минимум всех идентификаторов в хэштеге, которые имеют то же имя (следующий выбор), чтобы затем соответствующим образом обновить hashtag_id. В этом случае он также обновит записи с помощью hashtag_id 1947 и 1950 - но новое значение будет идентично старому значению.

update HashTaggedGroup
 set hashtag_id = 
    (select min(id) 
     from hashtag h1 
     where (
        select name 
        from hashtag h2 
        where h2.id=HashTaggedGroup.hashtag_id)=h1.name);


update HashTaggedActivity
 set hashtag_id = 
    (select min(id) 
     from hashtag h1 
     where (
        select name 
        from hashtag h2 
        where h2.id=HashTaggedActivity.hashtag_id)=h1.name);

Удаление, как показано ниже, будет работать для Mysql и SQLServer, может потребоваться корректировка для других БД (хотя идея остается прежней). Если вы уверены, что все идентификаторы из хэштега присутствуют в HashTaggedActivity, это позволит упростить запрос.

delete h1 from hashtag as h1 
    inner join hashtag as h2 on 
              h1.name = h2.name and 
              h1.id > h2.id;

SQLFiddle для вышеупомянутого

Источник

Я бы использовал оконные функции:

with ht as (
      select ht.*, min(id) over (partition by name) as minid
      from hashtag ht
     )
update hta
    set hashtag_id = ht.minid
    from HashTaggedActivity hta join
         ht
         on hta.hashtag_id = ht.id
    where ht.minid <> hta.hashtag_id;

А затем выполните удаление аналогичным образом:

with ht as (
      select ht.*, min(id) over (partition by name) as minid
      from hashtag ht
     )
delete from ht
    where ht.minid <> id;
Источник
Другие вопросы по тегам