时间戳

首页 > 解决方案 > 从表中删除重复项并将引用行重新链接到新的主数据

sql - 从表中删除重复项并将引用行重新链接到新的主数据

问题描述

我有一个表格transcription，其中包含转录文本的段落及其引用的列：

text, transcription_id(PK), t_notes, citation

第二个表town_transcription是将文本中引用的位置（来自另一个表）链接到该转录记录的关系表。该表具有以下列：

town_id(FK), transcription_id(FK), confidence_interval

许多这些文本段落引用了多个城镇，但愚蠢的是，我只是复制记录并将它们单独链接到每个城镇。我使用以下 SQL 查询识别了重复的文本行：

SELECT * FROM transcription aa
WHERE (select count(*) from transcription bb
WHERE (bb.text = aa.text) AND (bb.citation = aa.citation)) > 1
ORDER BY text ASC;

我现在有大约 2000 行（一些文本段落的 2 到 6 个重复项），我需要从表中删除多余transcription_id的 's并从关系表中transcription更改, 以指向剩余的、现在唯一的转录记录。通过阅读其他问题，我认为利用并且可能是必要的，但我真的不知道如何实现这一点，我只是一个初学者，感谢您的帮助。transcription_idtown_transcriptionUPDATE FROMINNER JOIN

标签： sqlpostgresqlduplicatescommon-table-expression

解决方案

这个单一的命令应该做到这一切：

WITH blacklist AS (  -- identify duplicate IDs and their master
   SELECT *
   FROM  (
      SELECT transcription_id
           , min(transcription_id) OVER (PARTITION BY text, citation) AS master_id
      FROM   transcription
      ) sub
   WHERE  transcription_id <> master_id
   )
, upd AS (  -- redirect referencing rows
   UPDATE town_transcription tt
   SET    transcription_id = b.master_id
   FROM   blacklist b
   WHERE  b.transcription_id = tt.transcription_id
   )
DELETE FROM transcription t  -- kill dupes (now without reference)
USING  blacklist b
WHERE  b.transcription_id = t.transcription_id;

由于缺乏定义，我选择了每组 ID 最小的行作为幸存的主行。

除非您有非默认设置，否则 FK 约束不会妨碍您。详细解释：

删除欺骗后，您现在可能需要添加UNIQUE约束以防止再次发生相同的错误：

ALTER TABLE transcription
ADD CONSTRAINT transcription_uni UNIQUE (text, citation);

推荐阅读