CODEWITHSUNDEEP
mysql
J. Wadia
J. Wadia

Reputation: 37

remove duplicate rows from mysql table (slightly complicated)

I'm hoping that there is a simple way in mysql to do the following.

Let's say that I have a table called T1

+-------+-------+-------------+
| gene1 | gene2 | correlation |
+-------+-------+-------------+
| ABC   | DEF   | 0.1         |
+-------+-------+-------------+
| DEF   | ABC   | 0.1         |
+-------+-------+-------------+

I want to delete entries where gene1 and gene2 are transposed and correlation is the same.

For instance, IF (gene1 = 'ABC' AND gene2 = 'DEF' AND correlation = 0.1) then I would like to remove these entries.

Upvotes: 0

Views: 49

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

If you want a query, simply do:

select t.*
from t
where gene1 < gene2
union all
select t.*
from t
where gene1 > gene2 and
      not exists (select 1
                  from t t2
                  where t.gene1 = t2.gene2 and t.gene2 = t2.gene1 and
                        t.correlation = t2.correlation
                 );

If you actually want to delete the entries, you can do:

delete t
    from t join
         t t2
         on t.gene1 = t2.gene2 and t.gene2 = t.gene1 and
            t2.correlation = t.correlation and
            t.gene1 > t.gene2;

The final condition (>) is so you only delete one of the rows that match.

Upvotes: 2

Related Questions