0

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);
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Vasanth S
  • 13
  • 3

1 Answers1

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)
Usagi Miyamoto
  • 6,196
  • 1
  • 19
  • 33
  • Got it...Usagi...tq – Vasanth S May 24 '19 at 06:22
  • 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 ); – Nick May 24 '19 at 06:22