phpmyadmin Question posted in
The official documentation can be found here.

How can I remove all the duplicate rows including the original one in SQL? – Phpmyadmin

I have a table like this:

id   id2
1    435  
2    345
3    345
4    567
5    456
6    987
7    987

Here in id2 345 and 987 are repeating twice, so I want to delete both the rows. My new table should look like this:

id   id2
1    435  
4    567
5    456
Tags:

2

Answers


  1. 0

    You can remove them from a select just by using aggregation:

    select min(id), id2
from t
group by id2
having count(*) = 1;

    If you want to remove them from the table, use join and group by:

    delete t
    from t join
         (select id2, count(*) as cnt
          from t
          group by id2
         ) tt
         on t.id2 = tt.id2
    where cnt > 1;
    Login or Signup to reply.
  2. 0

    You can use not exists :

    select *
from table t
where not exists (select 1 from table t1 where t1.id2 = t.id2 and t1.id <> t.id);

    Same you can remove the records :

    delete t
from table t 
where exists (select 1 from table t1 where t1.id2 = t.id2 and t1.id <> t.id);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search