Page 1 of 1

Multiple Delete Syntax

Posted: Thu Aug 20, 2009 11:52 am
by jackpf
Hi all,

I've never had trouble deleting from multiple tables in the same query before...but this one is giving me grief.

Basically, for my forum, when a thread is deleted, I wish to delete all relating data from the subscriptions table, and the thread tracking table. For neatness' sake, I've been trying to do this in one query...

Here is a simplified representation of the two individual queries:
[sql]DELETE FROM `Thread_Tracking_Table` WHERE `Tracking_ID`='$id';[/sql]
[sql]DELETE FROM `Thread_Subscriptions_Table` WHERE `Subscription_ID`='$id';[/sql]
And this is my attempt at a single query, which only deletes the corresponding rows from the `Thread_Subscriptions_Table`, but doesn't delete anything from the tracking table:
[sql]DELETE S, D FROM `Thread_Subscriptions_Table` S, `Thread_Tracking_Table` D WHERE S.`Subscription_ID`='$id' AND D.`Tracking_ID`='$id';[/sql]

Any help would be awesome.

Thanks,
Jack.

Re: Multiple Delete Syntax

Posted: Thu Aug 20, 2009 12:11 pm
by jayshields
I don't know about your query but if you're using InnoDB you can implement cascading deletes using referential integrity.

Re: Multiple Delete Syntax

Posted: Thu Aug 20, 2009 12:24 pm
by jackpf
I'm using MyIsam actually.

Besides, I'm not entirely sure what that is...

Re: Multiple Delete Syntax

Posted: Thu Aug 20, 2009 12:51 pm
by Eran
Not sure what you're trying to achieve. Is there a relationship between the two tables? how would MySQL know which rows to delete? your syntax is similar to a cross-join, but for a delete statement...
Note that probably no row can satisfy your filtering criteria (match both where arguments)

If there is no relationship between the tables it's best to use two seperate queries.

Re: Multiple Delete Syntax

Posted: Thu Aug 20, 2009 1:11 pm
by jackpf
The only relationship is indirect, through the `Forum` table...as they all relate to the same thread. I guess that still counts as a relationship though right?

I've done stuff like this in updates...I would have thought it'd be similar with delete queries.

Come on pytrin...work your magic :P

Re: Multiple Delete Syntax

Posted: Thu Aug 20, 2009 1:24 pm
by Eran
Change the 'AND' to 'OR' in your where clause. I'd suggest you backup the tables beforehand just in case ;)

Re: Multiple Delete Syntax

Posted: Thu Aug 20, 2009 1:34 pm
by jackpf
That deletes everything from both tables :(
pytrin wrote:I'd suggest you backup the tables beforehand just in case ;)
I'm testing this on my local machine so it doesn't really matter :) Thanks for your consideration though :P

Re: Multiple Delete Syntax

Posted: Thu Aug 20, 2009 1:38 pm
by Eran
The problem is that you are performing the equivalent of a cross join with this update syntax. Without a relationship to filter rows on, this select fetches every possible combination of rows from both tables.
I'd suggest running separate queries - why the insistence on a single query?

Re: Multiple Delete Syntax

Posted: Thu Aug 20, 2009 1:41 pm
by jackpf
Oh this is weird. It's working now. When I was testing this on my laptop I could swear it wasn't working...

And idk. Cause it's neat :P
Well there is a relationship.

Instead, I could say:

[sql]WHERE S.`Subscription_ID`=D.`Tracking_ID` AND S.`Subscription_ID`='$id'[/sql]

Re: Multiple Delete Syntax

Posted: Thu Aug 20, 2009 1:42 pm
by Eran
makes more sense now :P

Re: Multiple Delete Syntax

Posted: Thu Aug 20, 2009 1:50 pm
by jackpf
Cool.

Yeah, I don't know why that wasn't working before.

Sorry to waste your time :P