need to 'invert' this DELETE query

simonk

Hi all,

I'm having real trouble with a DELETE operation. I can usually sort out my SQL woes one way or another but this one has me stumped.

I wish to delete rows in a table 'shortlist' where shortlists.username does not exist in users.username. (Background: I want to clean-out guest user shortlists (shopping cart type thing) who don't have a registered account).

The nearest I can get is this:

DELETE FROM shortlists USING shortlists,users WHERE shortlists.username = users.username;

which does the opposite of what i want, as expected. It leaves the rows which don't have a match for 'username' in table 'users'

I've tried changing the '=' operator to <> and != but then it just deletes everything!

I've tried as many of the documented examples as possible, but none do what they claim (most just give syntax errors, which I don't understand). I'm using 4.0.13.

Any help very gratefully received!

Thanks in advance

simonk

xblue

hi simonk,

being not an expert in mysql issues, I believe it might be possible to employ a left join to achive what you want.

at least the manual says "multiple-table DELETE statements can use any type of join allowed in SELECT statements, such as LEFT JOIN" (for multiple-table DELETE statements as supported by mysql>=4.0.0)

something like

DELETE 
FROM table1
USING 
LEFT JOIN table2 ON table1.id=table2.id
WHERE table2.id IS NULL;

hope I didn't get it mixed up, but maybe it gives you an idea? (sorry, really tired right now)

simonk

man, that's done it!

One minor correction - the left join clause required a left argument:

DELETE FROM shortlists
USING shortlists LEFT JOIN users
ON shortlists.username=users.username
WHERE users.username IS NULL

I didn't realise how joins worked in the context of DELETES but it actually makes sense. 🙂

I hope this helps other people too as I can't imagine it's an unusual task.

Excellent - many thanks xblue!

simonk

xblue

glad to hear that it did work that way.