Deleting Duplicate Keys

How to delete duplicate Keys through PL-SQL in single query.

Showing Answers 1 - 21 of 21 Answers

ramniwas

  • Sep 5th, 2011
 

Code
  1. DELETE duplicate_column FROM TABLE WHERE row_id NOT IN (SELECT max(row_id) FROM TABLE GROUP BY duplicate_column);

  Was this answer useful?  Yes

Pavan

  • Sep 7th, 2011
 

delete from where rowid in (select max/min(rowid) from group by having count(*)>1)

  Was this answer useful?  Yes

Art11

  • Sep 19th, 2011
 

Replace select with delete:

Code
  1. SELECT ename FROM emp1 e

  2.  WHERE ROWID > ( SELECT MIN(rowid)

  3.             FROM emp1

  4.             WHERE ename = e.ename)

  5.  

  6.  

  7. SELECT * FROM

  8.    (

  9.     SELECT empno, ename, job

  10.           ,RANK() OVER (PARTITION BY ename, job ORDER BY empno) AS SeqNo

  11.       FROM emp1

  12.     )

  13.  WHERE SeqNo > 1

  14. /

  15.  

  Was this answer useful?  Yes

iftekhar

  • Sep 20th, 2011
 

Code
  1. SELECT empno FROM  emp WHERE empno NOT IN(SELECT MIN(empno FROM emp GROUP BY empno);

  Was this answer useful?  Yes

Art11

  • Sep 21st, 2011
 

To make my prev. posts readable...

Replace select with delete:

SELECT ename FROM emp1 e WHERE ROWID > (SELECT MIN(rowid) FROM emp1 WHERE ename = e.ename)

  Was this answer useful?  Yes

Art11

  • Sep 21st, 2011
 

Assuming that ename and job is the same or First and Last name...

SELECT * FROM (SELECT empno, ename, job ,RANK() OVER (PARTITION BY ename, job ORDER BY empno) AS SeqNo FROM emp1) WHERE SeqNo > 1

  Was this answer useful?  Yes

anonymous

  • Sep 27th, 2011
 

Code
  1.  SQL> DELETE FROM table_name WHERE rowid NOT IN (SELECT max(rowid) FROM TABLE GROUP BY duplicate_values_field_name);

  2. OR

  3. SQL> DELETE duplicate_values_field_name dv FROM table_name ta WHERE rowid <(SELECT min(rowid)  FROM table_name tb WHERE ta.dv=tb.dv);

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions