Geek Answers Handbook

MySQL removes duplicate return values

I have a MySQL MyISAM table:

Friends table (id, friend_id):

fifteen

5, 1

2, 6

6, 2

3, 7

How to delete reverse records? If for record values ​​"1, 5" there is an entry with the values ​​"5, 1", I need to delete "5, 1".

Thanks for the help!

+5
source share
4 answers
DELETE F1
FROM friends F1, friends F2
WHERE F1.friend_id = F2.id
  AND F2.friend_id = F1.id
  AND F1.id > F1.friend_id

EDIT

A stronger syntax would be:

DELETE F1
FROM friends F1
  JOIN friends F2 ON F1.friend_id = F2.id AND F2.friend_id = F1.id
WHERE F1.id > F1.friend_id

But the execution time is the same.

In addition, I created this little script for a quick and dirty test.

Results:

No index:

Dalen: 600 => 400 rows. Time: 0.0274
Mark: 600 => 400 rows. Time: 0.4323
Frosty: 600 => 400 rows. Time: 0.4081
Nick: 600 => 400 rows. Time: 0.3201

id friend_id:

Dalen: 600 => 400 rows. Time: 0.0201
Mark: 600 => 400 rows. Time: 0.0095
Frosty: 600 => 400 rows. Time: 0.0059
Nick: 600 => 400 rows. Time: 0.3257

(id, friend_id):

Dalen: 600 => 400 rows. Time: 0.0168
Mark: 600 => 400 rows. Time: 0.0057
Frosty: 600 => 400 rows. Time: 0.0041
Nick: 600 => 400 rows. Time: 0.3209

:

  • : ,
  • Frosty: , (Mark , " ", tmp. , tmp, , )
+7

:

create temporary table tmp    
  select a.* from friends as a,friends as b 
  where a.id = b.friend_id 
    and a.friend_id = b.id    /* left out of original post */
    and a.id < b.id;

delete from friends using friends inner join tmp 
   on friends.id = tmp.id
  and friends.friend_id=tmp.friend_id;

, , .

EDIT: "create... select". , . !

+3
DELETE FROM friends
WHERE (id,friend_id) IN
(   
   SELECT * FROM
   (
      SELECT t1.id,t1.friend_id
      FROM friends t1 JOIN friends t2
         ON t1.id=t2.friend_id AND t1.friend_id = t2.id
      WHERE t1.id > t1.friend_id
   ) t3
)
+2
source 
create table friends (
id int,
friend_id int ) engine = myisam;

insert into friends values (1,5),(5,1),(2,6),(6,2),(3,7);


delete from friends where (id,friend_id) in
(select * from (
select id,friend_id from friends
union all
select friend_id,id from friends ) as t
where id > friend_id
group by id,friend_id
having count(*) >  1)
+1
source

More articles:


All Articles