mysql : delete query doesn't work with a subquery

Question

This query execution never ends.

delete from commande where bl in (
    select noBl from (
        select distinct noBl,num_commande from T_VENTE) s
    group by noBl
    having count(*)>2
);

I don't understand because the subquery

select noBl from (
        select distinct noBl,num_commande from T_VENTE) s
    group by noBl
    having count(*)>2

takes one second to achieve.

Column bl is indexed in table commande.

If someone has an idea...
Thanks in advance

score 0 · Answer 1 · answered Aug 23 '17 at 08:31

0

I am not sure what you are achieve.You need to give detailed information.
You need a conditional statement for delete is something like this.

DELETE FROM customer1 WHERE agent_code=ANY(SELECT agent_code FROM agents WHERE working_area='London');

How its work

first get records from agent_code from agents table and then result are used to perform a delete operation.

answered Aug 23 '17 at 08:31

Vasim Shaikh

4,485
2
23
52

the subquery gives a list of ids(noBl). I need to delete from table commande records that have an id contained in the list of ids of the subquery. – thomas Aug 23 '17 at 08:40
you need to create procedure – Vasim Shaikh Aug 23 '17 at 08:41

score 0 · Accepted Answer · answered Aug 23 '17 at 08:52

You have to use another level of subquery : check this answer

delete from commande where bl in (
    select noBl from (
        select noBl from (
            select distinct noBl,num_commande from T_VENTE) s
        group by noBl
        having count(*)>2
    ) as tmp
);

mysql : delete query doesn't work with a subquery

2 Answers2