Re-writing my query to delete from a select statement

Question

Here's my query which in theory would be what I want but I know that MySQL won't let you use the same table in the sub-select as in the delete:

DELETE FROM manifestPallet WHERE palletID IN 
( SELECT  manifestPallet.palletID 
            FROM manifestPallet
            LEFT JOIN manifestBox
            ON manifestPallet.palletID = manifestBox.palletID
            WHERE manifestBox.palletID IS NULL)

I know I can do the select first, then loop through the results in my php script and do delete queries but I think there is probably a more elegant solution using pure MySQL.

Extra info: What the query does is delete pallets from a manifest that are empty. A user creates pallets then places boxes on them, if they create a pallet but don't put any boxes on it then the pallet needs to be removed when the manifest is closed.

Possible duplicate [MySQL Error 1093](http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause) — bsoist, Apr 22 '14 at 13:47

score 4 · Accepted Answer · answered Apr 22 '14 at 13:43

4

DELETE manifestPallet
FROM   manifestPallet LEFT JOIN manifestBox USING (palletID)
WHERE  manifestBox.palletID IS NULL

answered Apr 22 '14 at 13:43

eggyal

122,705
18
212
237

That worked! I had a feeling I was over-thinking my problem although I haven't had experience with keyword USING – S.Mason Apr 22 '14 at 13:46
@S.Mason: `a JOIN b USING (col1, col2)` is just shorthand for `a JOIN b ON a.col1 = b.col1 AND a.col2 = b.col2`. The only difference is that, when doing `SELECT *`, the resultset contains one column only of each `col1` and `col2`. – eggyal Apr 22 '14 at 15:29

score 0 · Answer 2 · answered Apr 22 '14 at 13:43

You simply need to specify on which tables to apply the DELETE.

Delete only the manifestPallet rows:

DELETE `manifestPallet` FROM `manifestPallet` LEFT JOIN `manifestBox` ....

Delete the manifestPallet and manifestBox rows:

DELETE `manifestPallet`, `manifestBox` FROM `manifestPallet` LEFT JOIN `manifestBox` ....

Delete only the manifestBox rows:

DELETE `manifestBox` FROM `manifestPallet` LEFT JOIN `manifestBox` ....

score 0 · Answer 3 · answered Apr 22 '14 at 13:58

DELETE FROM manifestPallet mp
WHERE NOT EXISTS (
    SELECT *
    FROM manifestBox mbx
    WHERE mp.palletID = mbx.palletID
    );

Note: there is a semantic difference with the original query, since the IN( subselect) term removes NULLs (and duplicates). This will probaly not cause differneces in the actual results, since the palletID is probably a NOT NULLable (primary) key.

Re-writing my query to delete from a select statement

3 Answers3