delete query inner join having

Question

I made a delete query to delete all records in tbljournaalposten where the field volgr occurs more than once.

delete from tbljournaalposten
where tbljournaalposten.ID in(
   SELECT
      tbljournaalposten.ID
   FROM
      invoerdatum 
   INNER JOIN
      rabobank2_2 ON rabobank2_2.IBAN_BBAN = invoerdatum.IBAN_BBAN 
   INNER JOIN
      tbljournaalposten ON rabobank2_2.Volgnr = tbljournaalposten.Volgnr
   WHERE
      rabobank2_2.invoerdatum = invoerdatum.invoerdatum
   GROUP BY  tbljournaalposten.volgnr
   HAVING  tbljournaalposten.volgnr >1

   ORDER BY
      rabobank2_2.Datum DESC )

When I try the code in phpmyadmin nothing seems to happen. It is loading for a long time. But after 5 minutes the query stops. When I look in the table thet recors with volgnr > 1 are deleted. When I only try this code it works good and direct.

SELECT
  tbljournaalposten.ID
FROM
  invoerdatum 
INNER JOIN
  rabobank2_2 ON rabobank2_2.IBAN_BBAN = invoerdatum.IBAN_BBAN 
INNER JOIN
  tbljournaalposten ON rabobank2_2.Volgnr = tbljournaalposten.Volgnr
WHERE
  rabobank2_2.invoerdatum = invoerdatum.invoerdatum
GROUP BY   tbljournaalposten.volgnr
HAVING  tbljournaalposten.volgnr >1

ORDER BY
  rabobank2_2.Datum DESC

I tried sereral times. I expected that the recores with volgr >1 are deleted.

Answer 1

I changed the code and now it works. Thanks for the help.

delete from tbljournaalposten
where tbljournaalposten.ID in(
SELECT * FROM (
SELECT
  tbljournaalposten.ID
FROM
  invoerdatum 
INNER JOIN
  rabobank2_2 ON rabobank2_2.IBAN_BBAN = invoerdatum.IBAN_BBAN 
INNER JOIN
  tbljournaalposten ON rabobank2_2.Volgnr = tbljournaalposten.Volgnr
WHERE
  rabobank2_2.invoerdatum = invoerdatum.invoerdatum
GROUP BY  tbljournaalposten.volgnr
HAVING  count(DISTINCT tbljournaalposten.ID) > 1
) sub
);

Answer 2

This query may delete that rows, which you dont want, so just make sure to not lose data in tbljournaalposten, (create copy of table or something like).

I guess you need something like:

delete from tbljournaalposten
where tbljournaalposten.volgnr in(
    SELECT volgnr FROM (
        SELECT
          tbljournaalposten.volgnr
        FROM
          invoerdatum 
        INNER JOIN
          rabobank2_2 ON rabobank2_2.IBAN_BBAN = invoerdatum.IBAN_BBAN 
        INNER JOIN
          tbljournaalposten ON rabobank2_2.Volgnr = tbljournaalposten.Volgnr
        WHERE
          rabobank2_2.invoerdatum = invoerdatum.invoerdatum
        GROUP BY  tbljournaalposten.volgnr
        HAVING  count(DISTINCT tbljournaalposten.ID) > 1
    ) sub
);

Also ORDER BY in sub query does not make sense in this case.

Answer 3

I tried this code but now all records with Volgnr> 1 are deleted.

delete from tbljournaalposten
where tbljournaalposten.volgnr in(
SELECT * FROM (
    SELECT
      tbljournaalposten.volgnr
    FROM
      invoerdatum 
    INNER JOIN
      rabobank2_2 ON rabobank2_2.IBAN_BBAN = invoerdatum.IBAN_BBAN 
    INNER JOIN
      tbljournaalposten ON rabobank2_2.Volgnr = tbljournaalposten.Volgnr
    WHERE
      rabobank2_2.invoerdatum = invoerdatum.invoerdatum
    GROUP BY  tbljournaalposten.volgnr
    HAVING  count(DISTINCT tbljournaalposten.ID) > 1
) sub
 );