Delete from multiple tables query

Question

I want to delete all these results, from this query based on reserveringsnr.

(SELECT DISTINCT res.reserveringsnr, datum, begintijd, eindtijd, aantalpersonen, klantnr, betaald, tafr.tafelnr, menr.reserveringsnr, menr.menunr, menr.uniqueid FROM reserveringen as res
    INNER JOIN menus_regel as menr
    ON res.reserveringsnr = menr.reserveringsnr
    INNER JOIN tafels_regel AS tafr
    ON res.reserveringsnr = tafr.reserveringsnr
    WHERE res.reserveringsnr = '21')

But something like this doesnt seem to work

DELETE FROM 
(SELECT DISTINCT res.reserveringsnr, datum, begintijd, eindtijd, aantalpersonen, klantnr, betaald, tafr.tafelnr, menr.reserveringsnr, menr.menunr, menr.uniqueid FROM reserveringen as res
    INNER JOIN menus_regel as menr
    ON res.reserveringsnr = menr.reserveringsnr
    INNER JOIN tafels_regel AS tafr
    ON res.reserveringsnr = tafr.reserveringsnr
    WHERE res.reserveringsnr = '21')Delete
WHERE Delete.reserveringsnr = '21'

Anyone could push me a bit in the right direction? Thanks in advance!

Answer 1

the query below will delete all records on table reserveringen only.

DELETE  res
FROM    reserveringen as res
        INNER JOIN menus_regel as menr
            ON res.reserveringsnr = menr.reserveringsnr
        INNER JOIN tafels_regel AS tafr
            ON res.reserveringsnr = tafr.reserveringsnr
WHERE   res.reserveringsnr = '21'

but if you want to delete records from all tables where reserveringsnr matched up, then you need to specify all table names in DELETE clause

DELETE  res, menr, tafr
FROM    reserveringen as res
        INNER JOIN menus_regel as menr
            ON res.reserveringsnr = menr.reserveringsnr
        INNER JOIN tafels_regel AS tafr
            ON res.reserveringsnr = tafr.reserveringsnr
WHERE   res.reserveringsnr = '21'

Answer 2

If you have to do it in one line, then @JW's answer looks correct. However, I have preferred running these in separate lines for performance issues -- if you have lots of records in each table, the joins will take longer than the individual deletes.

DELETE 
FROM reserveringen  
WHERE reserveringsnr = '21';

DELETE 
FROM menus_regel 
WHERE reserveringsnr = '21';

DELETE 
FROM tafels_regel 
WHERE reserveringsnr = '21'

Here is a useful link on deletes though:

http://dev.mysql.com/doc/refman/5.0/en/delete.html