blocks|key|1415138|text|使用rowid伪列。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1415139|DELETE+FROM+your_table
WHERE+rowid+not+in
(SELECT+MIN(rowid)
FROM+your_table
GROUP+BY+column1,+column2,+column3);|code-block|syntax|javascript|1415140|其中，column1、column2和column3构成了每条记录的标识键。您可以列出所有列。|1415141|entityMap^0|2|5|0|0|3|7|B|7|J|7|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|S|8|@$9|T|A|U|B|C]|$9|V|A|W|B|C]|$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|M|3|-4|5|6|7|Z|8|@]|D|@]|E|$]]]|N|$]]

Use the <code>rowid</code> pseudocolumn.

<pre><code>DELETE FROM your_table
WHERE rowid not in
(SELECT MIN(rowid)
FROM your_table
GROUP BY column1, column2, column3);
</code></pre>

Where <code>column1</code>, <code>column2</code>, and <code>column3</code> make up the identifying key for each record. You might list all your columns.

blocks|key|1415285|text|来自Ask+Tom|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1415286|delete+from+t
+where+rowid+IN+(+select+rid
++++++++++++++++++++from+(select+rowid+rid,+
+++++++++++++++++++++++++++++++++row_number()+over+(partition+by+
+++++++++++++++++++++++++companyid,+agentid,+class+,+status,+terminationdate
+++++++++++++++++++++++++++++++++++order+by+rowid)+rn
++++++++++++++++++++++++++++from+t)
+++++++++++++++++++where+rn+<>+1);|code-block|syntax|javascript|1415287|(修复了漏掉的括号)|1415288|entityMap|0|LINK|mutability|MUTABLE|url|http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:15258974323143^0|2|7|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@$A|T|B|U|1|V]]|C|$]]|$1|D|3|E|5|F|7|W|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|X|8|@]|9|@]|C|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|C|$]]]|L|$M|$5|N|O|P|C|$Q|R]]]]

From <a href="http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:15258974323143" rel="noreferrer">Ask Tom</a>

<pre><code>delete from t
 where rowid IN ( select rid
 from (select rowid rid, 
 row_number() over (partition by 
 companyid, agentid, class , status, terminationdate
 order by rowid) rn
 from t)
 where rn &lt;&gt; 1);
</code></pre>

(fixed the missing parenthesis)

blocks|key|1629890|text|来自DevX.com|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1629891|DELETE+FROM+our_table
WHERE+rowid+not+in
(SELECT+MIN(rowid)
FROM+our_table
GROUP+BY+column1,+column2,+column3...)+;|code-block|syntax|javascript|1629892|其中column1、column2等是您要使用的密钥。|1629893|entityMap|0|LINK|mutability|MUTABLE|url|http://www.devx.com/tips/Tip/14665^0|2|8|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@$A|T|B|U|1|V]]|C|$]]|$1|D|3|E|5|F|7|W|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|X|8|@]|9|@]|C|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|C|$]]]|L|$M|$5|N|O|P|C|$Q|R]]]]

From <a href="http://www.devx.com/tips/Tip/14665" rel="noreferrer">DevX.com</a>:

<pre><code>DELETE FROM our_table
WHERE rowid not in
(SELECT MIN(rowid)
FROM our_table
GROUP BY column1, column2, column3...) ;
</code></pre>

Where column1, column2, etc. is the key you want to use.

blocks|key|1629961|text|DELETE+FROM+tablename+a
++++++WHERE+a.ROWID+>+ANY+(SELECT+b.ROWID
+++++++++++++++++++++++++++++FROM+tablename+b
++++++++++++++++++++++++++++WHERE+a.fieldname+=+b.fieldname
++++++++++++++++++++++++++++++AND+a.fieldname2+=+b.fieldname2)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1629962|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>DELETE FROM tablename a
 WHERE a.ROWID &gt; ANY (SELECT b.ROWID
 FROM tablename b
 WHERE a.fieldname = b.fieldname
 AND a.fieldname2 = b.fieldname2)
</code></pre>

blocks|key|1630237|text|解决方案1)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1630238|delete+from+emp
where+rowid+not+in
(select+max(rowid)+from+emp+group+by+empno);|code-block|syntax|javascript|1630239|解决方案2)|1630240|delete+from+emp+where+rowid+in
+++++++++++++++(
+++++++++++++++++select+rid+from
++++++++++++++++++(
++++++++++++++++++++select+rowid+rid,
++++++++++++++++++++++row_number()+over(partition+by+empno+order+by+empno)+rn
++++++++++++++++++++++from+emp
++++++++++++++++++)
++++++++++++++++where+rn+>+1
+++++++++++++++);|1630241|解决方案3)|1630242|delete+from+emp+e1
+++++++++where+rowid+not+in
++++++++++(select+max(rowid)+from+emp+e2
+++++++++++where+e1.empno+=+e2.empno+);+|1630243|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

<h1>Solution 1)</h1>

<pre><code>delete from emp
where rowid not in
(select max(rowid) from emp group by empno);
</code></pre>

<h1>Solution 2)</h1>

<pre><code>delete from emp where rowid in
 (
 select rid from
 (
 select rowid rid,
 row_number() over(partition by empno order by empno) rn
 from emp
 )
 where rn &gt; 1
 );
</code></pre>

<h1>Solution 3)</h1>

<pre><code>delete from emp e1
 where rowid not in
 (select max(rowid) from emp e2
 where e1.empno = e2.empno ); 
</code></pre>

blocks|key|1630023|text|将表t2创建为select+distinct+*+from+t1；|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1630024|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

create table t2 as select distinct * from t1;

blocks|key|1629939|text|您应该使用游标for循环执行一个小的pl/sql块，并删除不想保留的行。例如：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1629940|declare
prev_var+my_table.var1%25TYPE;

begin

for+t+in+(select+var1+from+my_table+order+by+var+1)+LOOP

--+if+previous+var+equal+current+var,+delete+the+row,+else+keep+on+going.
end+loop;

end;|code-block|syntax|javascript|1629941|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You should do a small pl/sql block using a cursor for loop and delete the rows you don't want to keep. For instance:

<pre><code>declare
prev_var my_table.var1%TYPE;

begin

for t in (select var1 from my_table order by var 1) LOOP

-- if previous var equal current var, delete the row, else keep on going.
end loop;

end;
</code></pre>

blocks|key|1415415|text|要选择重复项，只能使用以下查询格式：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1415416|SELECT+GroupFunction(column1),+GroupFunction(column2),...,+
COUNT(column1),+column1,+column2...
FROM+our_table
GROUP+BY+column1,+column2,+column3...
HAVING+COUNT(column1)+>+1|code-block|syntax|javascript|1415417|因此，根据其他建议，正确的查询是：|1415418|DELETE+FROM+tablename+a
++++++WHERE+a.ROWID+>+ANY+(SELECT+b.ROWID
+++++++++++++++++++++++++++++FROM+tablename+b
++++++++++++++++++++++++++++WHERE+a.fieldname+=+b.fieldname
++++++++++++++++++++++++++++++AND+a.fieldname2+=+b.fieldname2
++++++++++++++++++++++++++++++AND+....so+on..+to+identify+the+duplicate+rows....)|1415419|此查询将根据WHERE+CLAUSE中选择的条件在数据库中保留最旧的记录。|offset|length|style|CODE|1415420|甲骨文认证助理(2008)|1415421|entityMap^0|0|0|0|0|6|C|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|Y|8|@$M|Z|N|10|O|P]]|9|@]|A|$]]|$1|Q|3|R|5|6|7|11|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

To select the duplicates only the query format can be:

<pre><code>SELECT GroupFunction(column1), GroupFunction(column2),..., 
COUNT(column1), column1, column2...
FROM our_table
GROUP BY column1, column2, column3...
HAVING COUNT(column1) &gt; 1
</code></pre>

So the correct query as per other suggestion is:

<pre><code>DELETE FROM tablename a
 WHERE a.ROWID &gt; ANY (SELECT b.ROWID
 FROM tablename b
 WHERE a.fieldname = b.fieldname
 AND a.fieldname2 = b.fieldname2
 AND ....so on.. to identify the duplicate rows....)
</code></pre>

This query will keep the oldest record in the database for the criteria chosen in the <code>WHERE CLAUSE</code>.

Oracle Certified Associate (2008)

blocks|key|1630347|text|create+table+abcd(id+number(10),name+varchar2(20))

insert+into+abcd+values(1,'abc')

insert+into+abcd+values(2,'pqr')


insert+into+abcd+values(3,'xyz')

insert+into+abcd+values(1,'abc')

insert+into+abcd+values(2,'pqr')

insert+into+abcd+values(3,'xyz')


select+*+from+abcd
id++Name
1+++abc
2+++pqr
3+++xyz
1+++abc
2+++pqr
3+++xyz

Delete+Duplicate+record+but+keep+Distinct+Record+in+table+

DELETE+
FROM+abcd+a
WHERE+ROWID+>+(SELECT+MIN(ROWID)+FROM+abcd+b
WHERE+b.id=a.id
);

run+the+above+query+3+rows+delete+

select+*+from+abcd

id++Name+
1+++abc
2+++pqr
3+++xyz|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1630348|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>create table abcd(id number(10),name varchar2(20))

insert into abcd values(1,'abc')

insert into abcd values(2,'pqr')


insert into abcd values(3,'xyz')

insert into abcd values(1,'abc')

insert into abcd values(2,'pqr')

insert into abcd values(3,'xyz')


select * from abcd
id Name
1 abc
2 pqr
3 xyz
1 abc
2 pqr
3 xyz

Delete Duplicate record but keep Distinct Record in table 

DELETE 
FROM abcd a
WHERE ROWID &gt; (SELECT MIN(ROWID) FROM abcd b
WHERE b.id=a.id
);

run the above query 3 rows delete 

select * from abcd

id Name 
1 abc
2 pqr
3 xyz
</code></pre>

blocks|key|1415486|text|使用rowid-|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1415487|delete+from+emp
+where+rowid+not+in
+(select+max(rowid)+from+emp+group+by+empno);|code-block|syntax|javascript|1415488|使用自连接-|1415489|delete+from+emp+e1
+where+rowid+not+in
+(select+max(rowid)+from+emp+e2
+where+e1.empno+=+e2.empno+);|1415490|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Using rowid-

<pre><code>delete from emp
 where rowid not in
 (select max(rowid) from emp group by empno);
</code></pre>

Using self join-

<pre><code>delete from emp e1
 where rowid not in
 (select max(rowid) from emp e2
 where e1.empno = e2.empno );
</code></pre>

blocks|key|1415583|text|解决方案4)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1415584|+delete+from+emp+where+rowid+in
++++++++++++(
+++++++++++++select+rid+from
++++++++++++++++(
++++++++++++++++++select+rowid+rid,
++++++++++++++++++dense_rank()+over(partition+by+empno+order+by+rowid
++++++++++++++++)+rn
+++++++++++++from+emp
++++++++++++)
+where+rn+>+1
);|code-block|syntax|javascript|1415585|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

<h1>Solution 4)</h1>
<pre><code> delete from emp where rowid in
 (
 select rid from
 (
 select rowid rid,
 dense_rank() over(partition by empno order by rowid
 ) rn
 from emp
 )
 where rn &gt; 1
);
</code></pre>

blocks|key|1630331|text|DELETE+from+table_name+where+rowid+not+in+(select+min(rowid)+FROM+table_name+group+by+column_name);|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1630332|您还可以通过另一种方式删除重复记录|unstyled|1630333|DELETE+from+table_name+a+where+rowid+>+(select+min(rowid)+FROM+table_name+b+where+a.column=b.column);|1630334|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|L|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$B|C]]|$1|I|3|-4|5|F|7|N|8|@]|9|@]|A|$]]]|J|$]]

<pre><code>DELETE from table_name where rowid not in (select min(rowid) FROM table_name group by column_name);
</code></pre>

and you can also delete duplicate records in another way

<pre><code>DELETE from table_name a where rowid &gt; (select min(rowid) FROM table_name b where a.column=b.column);
</code></pre>

blocks|key|1630052|text|DELETE+FROM+tableName++WHERE+ROWID+NOT+IN+(SELECT+++MIN+(ROWID)+FROM+table+GROUP+BY+columnname);|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1630053|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>DELETE FROM tableName WHERE ROWID NOT IN (SELECT MIN (ROWID) FROM table GROUP BY columnname);
</code></pre>

blocks|key|1415368|text|delete+from+dept
where+rowid+in+(
+++++select+rowid
+++++from+dept
+++++minus
+++++select+max(rowid)
+++++from+dept
+++++group+by+DEPTNO,+DNAME,+LOC
);|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1415369|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>delete from dept
where rowid in (
 select rowid
 from dept
 minus
 select max(rowid)
 from dept
 group by DEPTNO, DNAME, LOC
);
</code></pre>

blocks|key|1415463|text|为了获得最佳性能，我写了以下内容：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1415464|(参见执行计划)|1415465|DELETE+FROM+your_table
WHERE+rowid+IN+
++(select+t1.rowid+from+your_table++t1
++++++LEFT+OUTER+JOIN+(
++++++SELECT+MIN(rowid)+as+rowid,+column1,column2,+column3
++++++FROM+your_table+
++++++GROUP+BY+column1,+column2,+column3
++)++co1+ON+(t1.rowid+=+co1.rowid)
++WHERE+co1.rowid+IS+NULL
);|code-block|syntax|javascript|1415466|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

For best performance, here is what I wrote : 
(see execution plan)

<pre><code>DELETE FROM your_table
WHERE rowid IN 
 (select t1.rowid from your_table t1
 LEFT OUTER JOIN (
 SELECT MIN(rowid) as rowid, column1,column2, column3
 FROM your_table 
 GROUP BY column1, column2, column3
 ) co1 ON (t1.rowid = co1.rowid)
 WHERE co1.rowid IS NULL
);
</code></pre>

blocks|key|1415722|text|我没有看到任何使用通用表表达式和窗口函数的答案。这是我发现最容易使用的方法。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1415723|DELETE+FROM
+YourTable
WHERE
+ROWID+IN
++++(WITH+Duplicates
++++++++++AS+(SELECT
+++++++++++++++ROWID+RID,+
+++++++++++++++ROW_NUMBER()+
+++++++++++++++OVER(
+++++++++++++++PARTITION+BY+First_Name,+Last_Name,+Birth_Date)
++++++++++++++++++AS+RN
+++++++++++++++SUM(1)
+++++++++++++++OVER(
+++++++++++++++PARTITION+BY+First_Name,+Last_Name,+Birth_Date
+++++++++++++++ORDER+BY+ROWID+ROWS+BETWEEN+UNBOUNDED+PRECEDING+
+++++++++++++++++++++++++++++++++++++++AND+UNBOUNDED+FOLLOWING)
+++++++++++++++++++AS+CNT
++++++++++++++FROM
+++++++++++++++YourTable
++++++++++++++WHERE
+++++++++++++++Load_Date+IS+NULL)
+++++SELECT
++++++RID
+++++FROM
++++++duplicates
+++++WHERE
++++++RN+>+1);|code-block|syntax|javascript|1415724|需要注意的事情：|1415725|1)我们只检查partition子句中的字段是否重复。|1415726|2)如果您有某种理由选择一个副本，您可以使用order+by子句使该行的row_number()+=1|1415727|3)您可以通过将最后的where子句更改为"Where+RN+>+N“并使用N+>=+1来更改保留的重复数(我认为N=0会删除所有有重复的行，但它只会删除所有行)。|1415728|4)在CTE查询中添加了Sum分区字段，该字段将用组中的行数标记每一行。因此，要选择具有重复项的行，包括第一项，请使用"WHERE+cnt+>+1“。|1415729|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|V|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|W|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|Y|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

I didn't see any answers that use common table expressions and window functions.
This is what I find easiest to work with. 

<pre><code>DELETE FROM
 YourTable
WHERE
 ROWID IN
 (WITH Duplicates
 AS (SELECT
 ROWID RID, 
 ROW_NUMBER() 
 OVER(
 PARTITION BY First_Name, Last_Name, Birth_Date)
 AS RN
 SUM(1)
 OVER(
 PARTITION BY First_Name, Last_Name, Birth_Date
 ORDER BY ROWID ROWS BETWEEN UNBOUNDED PRECEDING 
 AND UNBOUNDED FOLLOWING)
 AS CNT
 FROM
 YourTable
 WHERE
 Load_Date IS NULL)
 SELECT
 RID
 FROM
 duplicates
 WHERE
 RN &gt; 1);
</code></pre>

Somethings to note:

1) We are only checking for duplication on the fields in the partition clause.

2) If you have some reason to pick one duplicate over others you can use an order by clause to make that row will have row_number() = 1

3) You can change the number duplicate preserved by changing the final where clause to "Where RN > N" with N >= 1 (I was thinking N = 0 would delete all rows that have duplicates, but it would just delete all rows).

4) Added the Sum partition field the CTE query which will tag each row with the number rows in the group. So to select rows with duplicates, including the first item use "WHERE cnt > 1".

blocks|key|1415341|text|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;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1415342|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>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 &gt; any
 (select rowid
 from enquiry
 where enquiry.enquiryno = z.enquiryno);
 end loop;
 end delete_duplicate_enq;
</code></pre>

blocks|key|1415731|text|解决方案：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1415732|delete+from+emp+where+rowid+in
(
++++select+rid+from
++++(
++++++++select+rowid+rid,
++++++++row_number()+over(partition+by+empno+order+by+empno)+rn
++++++++from+emp
++++)
++++where+rn+>+1
);|code-block|syntax|javascript|1415733|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

solution :

<pre><code>delete from emp where rowid in
(
 select rid from
 (
 select rowid rid,
 row_number() over(partition by empno order by empno) rn
 from emp
 )
 where rn &gt; 1
);
</code></pre>

blocks|key|1630566|text|这与上面的答案类似，但给了我一个更好的解释方案：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1630567|delete+from+your_table
+where+rowid+in+(
++++++++select+max(rowid)
++++++++++from+your_table
+++++++++group+by+column1,+column2,+column3
++++++++having+count(*)+>+1
+++++++);|code-block|syntax|javascript|1630568|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This is similar to the top answer but gives me a much better explain plan:
<pre><code>delete from your_table
 where rowid in (
 select max(rowid)
 from your_table
 group by column1, column2, column3
 having count(*) &gt; 1
 );
</code></pre>

I'm testing something in Oracle and populated a table with some sample data, but in the process I accidentally loaded duplicate records, so now I can't create a primary key using some of the columns.

How can I delete all duplicate rows and leave only one of them?

Removing duplicate rows from table in Oracle

数据库

我在Oracle中测试了一些东西，并用一些示例数据填充了一个表，但是在这个过程中，我意外地加载了重复的记录，所以现在我不能使用一些列创建主键。如何删除所有重复的行并只保留其中的一行？

问从Oracle表中删除重复行
EN

回答 19

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问从Oracle表中删除重复行EN

回答 19

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问从Oracle表中删除重复行
EN