Join In a Delete Query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Join In a Delete Query

Post by jackpf »

Hello everyone :)

Right, I have my forum. For my forum, I have a "subscriptions" table, where users can subscribe to a thread, and be PMed when new replies are posted in it.

When threads are deleted, I attempt to delete all subscriptions as well, just to keep things quick and tidy. However, I've been trying to make a function to delete all subscriptions to non-existent threads, which can be say....run by an admin as part of an "Optimize tables" operation or something.

Anyway, the subscriptions table is a many to many relationship (I think :/). So multple users can subscribe to multiple threads, and multiple threads can have multiple subscriptions.

Now, Instead of selecting all subscriptions, and looping through them to see if the relating thread exists, I was hoping to achieve the same effect with a join.

I tried:

Code: Select all

DELETE S #delete from subscriptions (alias S)
FROM `Subscriptions` S #...
LEFT OUTER JOIN `Forum` F ON S.`Subscription_ID` = F.`ID` #join the forum table on the subscription id
WHERE S.`Subscription_ID`!=F.`ID`#delete where the subscription id does not match a forum id (does not work!)
Yeah, this doesn't work...
I've managed to successfully delete all subscriptions that do exist...but that's the opposite of the desired effect.

Anyway, any help would be awesome.
Thanks a lot,
Jack.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Join In a Delete Query

Post by Eran »

Code: Select all

...
LEFT JOIN `Forum` F ON S.`Subscription_ID` = F.`ID`
WHERE F.`ID` IS NULL
should do it.
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: Join In a Delete Query

Post by jackpf »

Oh, awesome.

Thanks a lot pytrin; once again, you are my saviour :bow:



:P
Cheers,
Jack.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Join In a Delete Query

Post by Eran »

glad to be of help :)
Post Reply