Delete query - mysql

Question

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?

Huh? You have conflicting requirements in your question. You want to delete all rows which contain name2=E, yet the table under your desired results has deleted F,G,H, and I. You can't have it both ways. — Sam M
– Sam M, Commented May 10, 2016 at 3:50

Gordon Linoff · Accepted Answer · 2016-05-10 02:54:06Z

3

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

edited May 10, 2016 at 2:54

answered May 10, 2016 at 2:43

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

3 Comments

Felix Pamittan Over a year ago

Up for the MySQL subquery restriction.

dPdms Over a year ago

If I would like to do select instead of delete, what should I do? : )

Jim Garrison Over a year ago

As it stands right now the question asks about a single table. Did it previously mention two tables?

Collectives™ on Stack Overflow

Delete query - mysql

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related