CODEWITHSUNDEEP
mysqlsqlsql-delete
dPdms
dPdms

Reputation: 173

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?

Upvotes: 0

Views: 257

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

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);

Upvotes: 3

Related Questions