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.

How many records are returned from the sub query ? when that number is high you might do a WHERE NOT IN( select records you want to keep ) — Luuk
– Luuk, Commented Dec 17, 2023 at 14:22

Oto Shavadze · Accepted Answer · 2023-12-18 14:58:45Z

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.

edited Dec 18, 2023 at 14:58

answered Dec 17, 2023 at 14:15

Oto Shavadze

43.3k56 gold badges169 silver badges248 bronze badges

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

4 Comments

Johan Over a year ago

Thanks for the code but it doesn't work. I also have the same problem. When I only try the sub query it gives me 2 records.

P.Salmon Over a year ago

This query should throw and error You can't specify target table 'tbljournaalposten' for update in FROM clause..dropping the select down a level should help ie in( select * from (SELECT tbljournaalposten.volgnr ...) s

Oto Shavadze Over a year ago

@Johan - Did you get error which P.Salmon mentioned? Just updated answer, so check please if that works now? and if not ,what do you getting? some error? or just not removing expected rows ?

Oto Shavadze Over a year ago

@P.Salmon - Thanks for mentionig that, I did not tested query, just updated answer.

Johan · Accepted Answer · 2023-12-18 20:31:36Z

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

answered Dec 18, 2023 at 20:31

Johan

215 bronze badges

Comments

Johan · Accepted Answer · 2023-12-19 17:22:51Z

0

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

answered Dec 19, 2023 at 17:22

Johan

215 bronze badges

Collectives™ on Stack Overflow

delete query inner join having

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related