从Oracle表中删除重复行

Question

从Oracle表中删除重复行

sqloracleduplicatesdelete-row

198

我在Oracle中正在测试某些内容，并使用一些示例数据填充了一个表格，但是在此过程中意外加载了重复记录，因此现在我无法使用某些列创建主键。

如何删除所有重复行并仅保留其中一个？

- Juan Manuel

24个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- user3655760 · Answer 1

delete from dept
where rowid in (
     select rowid
     from dept
     minus
     select max(rowid)
     from dept
     group by DEPTNO, DNAME, LOC
);

- JgSudhakar · Answer 2

DELETE FROM tableName  WHERE ROWID NOT IN (SELECT   MIN (ROWID) FROM table GROUP BY columnname);

- Howd · Answer 3

这与顶部答案类似，但给了我一个更好的执行计划：

delete from your_table
 where rowid in (
        select max(rowid)
          from your_table
         group by column1, column2, column3
        having count(*) > 1
       );

- Ashish sinha · Answer 4

create or replace procedure delete_duplicate_enq as
    cursor c1 is
    select *
    from enquiry;
begin
    for z in c1 loop
        delete enquiry
        where enquiry.enquiryno = z.enquiryno
        and rowid > any
        (select rowid
        from enquiry
        where enquiry.enquiryno = z.enquiryno);
    end loop;
 end delete_duplicate_enq;