deleting duplicate rows in single given column in mysql without any ids

Question

EMP
-------
EMPNO
-----------
1
1
2
3
3
4
4
4
5
6

Write a DELETE statement to delete the duplicate rows. After running your Delete statement, one occurrence of each value of Empno should remain in the table.

delete a from emp1 a where empno in(select empno from ( select empno from emp1 group by empno having count(empno)>1) as net) ; — pankaj, Sep 01 '17 at 12:17
i am beginner in this, can do simple operations in mysql and mongodb not the very complex one — pankaj, Sep 01 '17 at 12:23
yeah i have seen this link before but there we have distinct id to compare but not here — pankaj, Sep 01 '17 at 12:31
This hypothetical problem could not occur in a normalised environment. Therefore, I reject the premise of the question. — Strawberry, Sep 01 '17 at 12:32
You can't write delete query for this because you don't have a unique id or timestamp — Raymond Nijland, Sep 01 '17 at 12:44

Naincy · Answer 1 · 2017-09-01T12:53:58.187

-1

simplest thing is define unique constraint on that column so it will not take duplicate values and if there are then it will auto delete

Or Try below query

DELETE e.*
FROM emp1 e
WHERE empno IN
 (SELECT empno
   FROM (SELECT MIN(e1.empno) as empno
          FROM emp1 e1
          GROUP BY e1.empno
          HAVING COUNT(*) > 1) x);

Or you can see this example also Delete all Duplicate Rows except for One in MySQL?

edited Sep 01 '17 at 12:53

answered Sep 01 '17 at 12:22

Naincy

2,953
1
12
21

not working, empno in group statement is ambiguous.....this deleted all duplicates – pankaj Sep 01 '17 at 12:26
updated my answer – Naincy Sep 01 '17 at 12:27
is that worked for you? if not then please share DB structure with values – Naincy Sep 01 '17 at 12:32
create table EMP1 (EMPNO numeric(2) not null); insert into EMP1 values(1); insert into EMP1 values(1); insert into EMP1 values(2); insert into EMP1 values(3); insert into EMP1 values(3); insert into EMP1 values(4); insert into EMP1 values(4); insert into EMP1 values(4); insert into EMP1 values(5); insert into EMP1 values(6); select * from emp1; DELETE e.* FROM emp1 e WHERE empno IN (SELECT empno FROM (SELECT MIN(empno) as empno FROM emp1 GROUP BY empno HAVING COUNT(*) > 1) x); use mtech; – pankaj Sep 01 '17 at 12:39
are we able to do this in other sql like oracle? – pankaj Sep 01 '17 at 12:46
@pankaj Why would we? – Strawberry Sep 01 '17 at 12:50
i mean may be there some clauses which are there to solve it but not in mysql – pankaj Sep 01 '17 at 12:52
why you need such table with one column I have no idea....simplest thing is define unique constraint on that column so it will not take duplicate values and if there are then it will auto .delete – Naincy Sep 01 '17 at 12:53
@pankaj But the problem is so specific and improbable. – Strawberry Sep 01 '17 at 12:55
yeah i understand you – pankaj Sep 01 '17 at 12:57
1

I don't think you do :-( – Strawberry Sep 01 '17 at 12:57
@Naincy if you believe that another SO question already has the answer for the current one, then flag it as a duplicate, do not answer! – Shadow Sep 01 '17 at 13:21

deleting duplicate rows in single given column in mysql without any ids

1 Answers1