To delete duplicate rows in Oracle SQL [duplicate]

Question

I've used this code to select the duplicate rows, it went right,

SELECT name, COUNT(name) 
  FROM emp
 GROUP BY name 
HAVING COUNT>1;

But it doesn't help me in deleting the same selected rows....

DELETE emp 
 WHERE name IN ( SELECT name, COUNT(name) 
                   FROM emp 
                  GROUP BY name 
                 HAVING COUNT >1);

Usagi Miyamoto · Accepted Answer · 2019-05-24 06:01:58Z

0

Remove the second column from the inner SELECT, something like this:

DELETE
  FROM emp
  WHERE name IN( SELECT name FROM emp GROPU BY name HAVING COUNT(name) > 1)

answered May 24, 2019 at 6:01

Usagi Miyamoto

6,3471 gold badge21 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vasanth S Over a year ago

Got it...Usagi...tq

Nick Over a year ago

I've added a rownum to retain 1 record. DELETE FROM emp a WHERE a.name IN( SELECT b.name FROM emp b where a.name = b.name GROUP BY name HAVING COUNT(b.name) > 1) and rownum IN( SELECT count(c.name) - 1 FROM emp c where a.name = c.name GROUP BY c.name );

Collectives™ on Stack Overflow

To delete duplicate rows in Oracle SQL [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related