SQL : issue with delete query that never ends-CodePudding

This query works fine and fast :

select t.id_facture from
    (SELECT distinct id_facture, num_commande 
     FROM table) t
group by t.id_facture
having count(*)>1;

But when I want to use it in order to delete records

delete from table where id_facture in (
   select t.id_facture from
       (SELECT distinct id_facture, num_commande 
         FROM table) t
   group by t.id_facture
   having count(*)>1 );

it lasts forever and end up with an error :

Error Code: 2013. Lost connection to MySQL server during query

Is there another to perform this delete query?

id_facture  num_commande
1           2
1           3
2           4
2           4
3           5
3           6

I want to delete rows with id_facture : 1 and 3 because they have differents num_commande related. id_facture 2 has only one associated num_commande, so I want to keep it.

CodePudding user response：

DELETE FROM t
WHERE id_facture IN (
    SELECT id_facture
    FROM (
        SELECT t.id_facture
        FROM t
        GROUP BY t.id_facture
        HAVING MIN(t.num_commande) <> MAX(t.num_commande)
    ) AS x
)

Could you please try the above #TO_DELETE is your table

CodePudding user response：

Use a JOIN instead of IN()...

DELETE
  example
FROM
  example
INNER JOIN
(
  SELECT id_facture
    FROM example
GROUP BY id_facture
  HAVING COUNT(DISTINCT num_commande) > 1
)
  dupes
    ON dupes.id_facture = example.id_facture
;

Demo : https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=b33532dc29c3f3d889890952d45f26af