Question 307309 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – delete query inner join having

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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

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

  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search