从表中删除重复项，并将引用行重新链接到新的母版
我有一个表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表中删除多余的transcription_id，并将关系表中的transcription_id更改为指向剩余的、现在唯一的转录记录。从阅读其他问题来看，我认为利用UPDATE FROM和INNER JOIN可能是必要的，但我真的不知道如何实现这一点，我只是一个初学者，谢谢你的帮助。
回答 2
Stack Overflow用户
发布于 2018-11-19 08:23:55
这一条命令应该可以完成所有的工作：
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约束不会妨碍您。详细说明：
- How to remove duplicate rows with foreign keys dependencies?
- Delete duplicates and reroute referencing rows to new master
删除重复项后，您现在可能需要添加一个UNIQUE约束，以防止再次发生相同的错误：
ALTER TABLE transcription
ADD CONSTRAINT transcription_uni UNIQUE (text, citation);Stack Overflow用户
发布于 2018-11-19 08:23:44
使用row_number() over(...)标识重复信息的行。over子句中的partition by text, citation将强制这些值的每个唯一集合的行号序列从1重新开始：
select
*
from (
select
text, transcription_id, t_notes, citation
, row_number() over(partition by text, citation
order by transcription_id) as rn
from transcription
) d
where rn > 1一旦将这些行验证为不需要的行，就可以对delete语句使用相同的逻辑。
但是，您可能会 t_notes列中包含的松散信息-您愿意这样做吗？
https://stackoverflow.com/questions/53366008
复制相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号