Delete operation in SQL with a Join in it

Question

I have two tables, user and ban. User table has two columns id and type. Ban table also has two columns userID and bannedUserID. I want to delete records in ban table where bannedUserID has type of 'Admin'.

I've come up with such query but I am not sure if it is correct or not.

DELETE FROM ban WHERE ban.bannedUserID IN (SELECT id FROM user WHERE type = 'Admin')

Is it correct? Do I have to add/remove anything?

Your query seems correct. WHat I wonder about is the usage of two columns, `userID` and `bannedUserID` in table `ban`. — ypercubeᵀᴹ, Jul 25 '11 at 17:01
It contains a user's ban list. For example, if a row like `12345` and `67890` exists that means User having id `12345` has banned user having id `67890`. User `12345` won't see any post of user `67890` — Sarpdoruk Tahmaz, Jul 25 '11 at 18:46

score 4 · Answer 1 · answered Jul 25 '11 at 16:49

If you're not sure whether the DELETE is correct, you can test it by converting to an equivalent SELECT:

SELECT b.bannedUserID 
FROM ban b JOIN user u ON b.bannedUserID = u.id
WHERE u.type = 'Admin';

Are the id's returned by that query the ones you want to DELETE?

Then try it in a transaction (if you use a storage engine that supports transactions, i.e. not MyISAM), so you can roll back if necessary:

START TRANSACTION;

DELETE b 
FROM ban b JOIN user u ON b.bannedUserID = u.id
WHERE u.type = 'Admin';

/* do some SELECT to see if the correct rows have been deleted and no others */
/* but if anything appears wrong, and ban is an InnoDB table, you can... */

ROLLBACK;

Note that I'm using MySQL's multi-table DELETE syntax. It should work identically to your subquery approach, but in some cases it can be more flexible.

Delete operation in SQL with a Join in it

1 Answers1