CODEWITHSUNDEEP
mysqlsql-delete
dPdms
dPdms

Reputation: 173

delete rows in mysql

If I have a table:

id1       id2       count
A        A          1
A        B          2
A        C          1
B        A          3
B        B          1
B        C          2
C        A          3
C        B          2
C        C          1

What I want after deleting:

id1       id2       count
A        A          1
A        B          2
A        C          1

B        B          1
B        C          2


C        C          1

which means if I have A(id1) --> B(id2) then delete B(id1) --> A(id2). same as B(id1) --> C(id2) then delete the row C(id1) --> B(id2)

Thank you for ur help!

Upvotes: 0

Views: 50

Answers (3)

spencer7593
spencer7593

Reputation: 108500

It looks like what you are saying is...

If there is a (id1,id2) tuple in the table with values e.g. (a,b), and there is another tuple (b,a) that consists of the the same values, but swapped in the columns, you want to remove one of those tuples. It looks like the one you want to remove is the one that has the "greater" value in the first column.

First, identify the "duplicate" tuples.

For now, we'll ignore the tuples where the values of id1 and id2 are the same, e.g. (a,a).

SELECT s.id1
     , s.id2
  FROM mytable s
 WHERE s.id1 > s.id2
   AND EXISTS ( SELECT 1
                  FROM mytable r
                 WHERE r.id1 = s.id2
                   AND r.id2 = s.id1
              )
 ORDER BY s.id1, s.id2

If that returns the set of rows you want to remove, we can convert that into a DELETE. To do that, we need to change that query into an inline view,

We can re-write that to be like this, verify we get equivalent results.

SELECT o.id1, o.id2 
  FROM ( SELECT q.id1, q.id2
           FROM ( SELECT s.id1, s.id2
                   FROM mytable s
                  WHERE s.id1 > s.id2
                    AND EXISTS ( SELECT 1
                                   FROM mytable r
                                  WHERE r.id1 = s.id2
                                    AND r.id2 = s.id1
                               )
                ) q
          GROUP BY q.id1, q.id2 
       ) p
  JOIN mytable o
    ON o.id1 = p.id1
   AND o.id2 = p.id2  
 ORDER BY o.id1, o.id2

Then we can convert that to a DELETE statement, replacing SELECT o.id1, o.id2 WITH DELETE o.* and removing the ORDER BY...

DELETE o.*
  FROM ( SELECT q.id1, q.id2
           FROM ( SELECT s.id1, s.id2
                   FROM mytable s
                  WHERE s.id1 > s.id2
                    AND EXISTS ( SELECT 1
                                   FROM mytable r
                                  WHERE r.id1 = s.id2
                                    AND r.id2 = s.id1
                               )
                ) q
          GROUP BY q.id1, q.id2 
       ) p
  JOIN mytable o
    ON o.id1 = p.id1
   AND o.id2 = p.id2

Upvotes: 1

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48207

  • In this case we analyze Target.id1 > Target.id2 mean case like (B, A, ??) where B > A
    • this also ignore cases like (A, A, ??)
  • Then use self left join to try find another row with (A, B, ??)
  • If we found a match then Source.id1 IS NOT NULL and we delete

SQL Fiddle Demo

DELETE Target 
FROM Table1 Target
LEFT JOIN Table1 Source 
       ON Target.`id1` = Source.`id2`
      AND Target.`id2` = Source.`id1`
      AND Target.`id1` > Target.`id2`
WHERE Source.`id1` IS NOT NULL;

OUTPUT

| id1 | id2 | count |
|-----|-----|-------|
|   A |   A |     1 |
|   A |   B |     2 |
|   A |   C |     1 |
|   B |   B |     1 |
|   B |   C |     2 |
|   C |   C |     1 |

Upvotes: 1

nhouser9
nhouser9

Reputation: 6780

Should be something like:

DELETE FROM 'myTable'
WHERE STRCMP(id1, id2) > 0;

STRCMP function can compare the strings and return an int. From there it should be easy - something very similar to the above. If you have further trouble let me know.

Upvotes: 1

Related Questions