dPdms dPdms - 6 months ago 8
SQL Question

Delete query - mysql

If I have a table such as:

name1 | name2 | id |
+----------------+--------------+-----------+
| A | E | 1 |
| A | F | 1 |
| B | G | 1 |
| C | H | 1 |
| D | I | 1 |
| A | J | 2 |
| B | K | 2 |
| C | L | 2 |
| D | M | 2 |
| A | N | 2 |


what I need is that delete all rows of id which contain name2 = E

If I do:

delete from table where name2 = E


It only gives me this

name1 | name2 | id |
+----------------+--------------+-----------+
| A | F | 1 |
| B | G | 1 |
| C | H | 1 |
| D | I | 1 |
| A | J | 2 |
| B | K | 2 |
| C | L | 2 |
| D | M | 2 |
| A | N | 2 |


The result I want is :

name1 | name2 | id |
+----------------+--------------+-----------+
| A | J | 2 |
| B | K | 2 |
| C | L | 2 |
| D | M | 2 |
| A | N | 2 |


Which query should I use?

Answer

I think you want something like this:

delete t
    from table t join
         table t2
         on t.id = t2.id and t2.name2 = 'E';

This deletes all rows from the table that share an id with a row whose name2 is 'E'.

In most other databases, you could write:

delete t from table t
    where t.id in (select t2.id from table t2 where t2.name2 = 'E');

Or something similar using exists. Unfortunately, MySQL does not allow this syntax because the subquery references the table being modified. There is a hack:

delete t from table t
    where t.id in (select id from (select t2.id from table t2 where t2.name2 = 'E') t);

I prefer the version with the join.

For a select, I would do:

select t.*
from table t
where t.id in (select t2.id from table t2 where t2.name2 = 'E');

or:

select t.*
from table t
where exists (select 1 from table t2 where t2.name2 = 'E' and t2.id = t.id);