CODEWITHSUNDEEP
mysqlsqlsql-delete
Thijmen
Thijmen

Reputation: 127

DELETE MySQL subquery

I have a query which generates a result:

SELECT urenID FROM uren WHERE uren.urenDossierID IN (SELECT dossierID FROM dossier WHERE dossierKlantID NOT IN (SELECT klantID from klant))

Now I want to delete all the records which generates that query. Now is my question; how to do that?

Upvotes: 0

Views: 77

Answers (3)

John Woo
John Woo

Reputation: 263723

I'm a strong believer that JOIN is faster than IN 

DELETE  a
FROM    uren a
        LEFT JOIN dossier b
            ON a.urenDossierID = b.dossierID
        LEFT JOIN klant c
            ON b.dossierKlantID = c.klantID
WHERE   b.dossierID IS NOT NULL AND
        c.klantID IS NULL

Upvotes: 1

sdespont
sdespont

Reputation: 14025

Just replace SELECT urenID by DELETE

DELETE FROM uren WHERE uren.urenDossierID IN (SELECT dossierID FROM dossier WHERE dossierKlantID NOT IN (SELECT klantID from klant))

Upvotes: 2

Iswanto San
Iswanto San

Reputation: 18569

Try this.

DELETE FROM uren WHERE urenID in (
   SELECT urenID FROM uren WHERE uren.urenDossierID IN (SELECT dossierID FROM dossier WHERE dossierKlantID NOT IN (SELECT klantID from klant)))

Upvotes: 1

Related Questions