甲骨文|删除重复记录 [英] oracle | delete duplicates records
本文介绍了甲骨文|删除重复记录的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在表中发现了一些重复项:
I have identified some duplicates in my table:
-- DUPLICATES: ----
select PPLP_NAME,
START_TIME,
END_TIME,
count(*)
from PPLP_LOAD_GENSTAT
group by PPLP_NAME,
START_TIME,
END_TIME
having count(*) > 1
-- DUPLICATES: ----
如何删除它们?
推荐答案
即使您没有主键,每个记录也会有一个唯一的关联的rowid.
Even if you don't have the primary key, each record has a unique rowid associated.
通过使用下面的查询,您可以通过将表与导致重复的列进行自我连接来仅删除没有最大行ID的记录.这样可以确保您删除所有重复项.
By using the query below you delete only the records that don't have the maximum row id by self joining a table with the columns that cause duplication. This will make sure that you delete any duplicates.
DELETE FROM PPLP_LOAD_GENSTAT plg_outer
WHERE ROWID NOT IN(
select MAX(ROWID)
from PPLP_LOAD_GENSTAT plg_inner
WHERE plg_outer.pplp_name = plg_inner.pplg_name
AND plg_outer.start_time= plg_inner.start_time
AND plg_outer.end_time = plg_inner.end_time
);
这篇关于甲骨文|删除重复记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文