Multiple Delete Syntax

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

Multiple Delete Syntax

Post 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.
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: Multiple Delete Syntax

Post by jayshields »

I don't know about your query but if you're using InnoDB you can implement cascading deletes using referential integrity.
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: Multiple Delete Syntax

Post by jackpf »

I'm using MyIsam actually.

Besides, I'm not entirely sure what that is...
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Multiple Delete Syntax

Post 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.
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: Multiple Delete Syntax

Post 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
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Multiple Delete Syntax

Post by Eran »

Change the 'AND' to 'OR' in your where clause. I'd suggest you backup the tables beforehand just in case ;)
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: Multiple Delete Syntax

Post 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
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Multiple Delete Syntax

Post 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?
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: Multiple Delete Syntax

Post 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]
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Multiple Delete Syntax

Post by Eran »

makes more sense now :P
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: Multiple Delete Syntax

Post by jackpf »

Cool.

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

Sorry to waste your time :P
Post Reply