甲骨文|删除重复记录 [英] 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屋！