View Full Version : Multiple table delete

08-08-2006, 12:51 PM

Here is my scenario: I want to be able to delete rows from three different tables, A, B, and C, using the the same two keys (1 and 2) for each one. The catch is that data might not exist in Table B, and even if it does, it might not exist in Table C (the query wouldn't exist if there was no matching row in Table A).

After much wailing and gnashing of teeth I came up with a query in this format:

DELETE A, B, C FROM A aa, B bb, C cc
WHERE aa.1 = '1'
AND bb.1 = '1'
AND cc.1 = '1'
AND aa.2 = '2'
AND bb.2 = '2'
AND cc.2 = '2'

The works great if matching data exists in all three tables, but not at all when matching data doesn't exist in B and/or C.

So is there a different syntax I can use or should I stick with three queries?

08-08-2006, 01:53 PM
any reason why you don't just run 3 deletes? which would be easier and probably faster...

any reason why you don't use an ON DELETE cascade on two of these tables?

08-08-2006, 02:38 PM
Well, I don't know these things so that's why I ask questions. :)

To me it would seem counter-intuitive that three queries would be faster but I'm not all that familiar with MySQL.

As for CASCADE, please correct me if I'm wrong, but that would be a setting in the database itself, not part of a query that would execute due to certain actions requested via a webpage. The database I'm using is based on what must now be an outdated structure and doesn't have constraints built into it but that would be very useful. Something to consider for the future.

Thanks for the tips- I'll stick with three queries for now.