delete rows in mysql

Question

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!

You need explain a litle better. Also the notation isnt clear. Please read How-to-Ask And here is a great place to START to learn how improve your question quality and get better answers. — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Apr 15, 2016 at 4:12
Something like this... If you have (A, B, ??) delete (B, A, ??) for every row where id1 < id2? — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Apr 15, 2016 at 4:15

Juan Carlos Oropeza · Accepted Answer · 2016-04-15 04:29:41Z

1

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 |

edited Apr 15, 2016 at 4:29

answered Apr 15, 2016 at 4:23

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

nhouser9 · Accepted Answer · 2016-04-15 04:14:07Z

1

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.

answered Apr 15, 2016 at 4:14

nhouser9

6,7803 gold badges23 silver badges43 bronze badges

1 Comment

Juan Carlos Oropeza Over a year ago

I dont think so. Because if you only have (B, A, ??) and not (A, B, ??)you shouldnt delete it

spencer7593 · Accepted Answer · 2016-04-15 04:33:57Z

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

Collectives™ on Stack Overflow

delete rows in mysql

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related