Welcome to WebmasterWorld Guest from 54.160.131.144

Forum Moderators: open

Message Too Old, No Replies

MySQL multi table deletes when data doesn't always exist in all tables

MySQL mutli table deletes

     

58sniper

7:06 pm on Oct 8, 2005 (gmt 0)

10+ Year Member



MySQL 4.1.x

I have the following tables:
psor_offenders
psor_offenses
psor_aliases

all have a common field, 'id'.

I need to delete from psor_offenses and psor_aliases where their respective ID field matches that in psor_offenders. In my test case, I'm limiting to those where psor_offenders.zip = '48313'.

I can do a delete on psor_offenses that match, or on psor_alises that match, but I can't seem to get it to work on both at the same time.

Using:
DELETE FROM psor_offenses, psor_aliases using psor_offenses, psor_aliases, psor_offenders WHERE psor_offenses.id = psor_offenders.id and psor_aliases.id = psor_offenders.id and psor_offenders.zip = '48313'

only appears to have deleted from the two tables when there were records in both with the same ID (a three-way match). I need to delete from both regardless of whether their are matches in the other (there is always a match in psor_offenses, but not always in psor_aliases).

Does this make sense? I'm new to multi table deletes, and haven't been able to figure this out.

txbakers

1:20 am on Oct 9, 2005 (gmt 0)

WebmasterWorld Senior Member txbakers is a WebmasterWorld Top Contributor of All Time 10+ Year Member



I'm not sure what you can delete if the match doesn't exist in the third table. The way your query is written is correct, (typo understood) so it will only delete where there is a match.

What do you want to delete if there isn't a match? Isn't that dangerous - you could delete everything that doesn't match.

You can also write three delete statements, which I always feel is safer.

58sniper

4:39 am on Oct 9, 2005 (gmt 0)

10+ Year Member



I don't want it to delete anything in the psor_aliases table if it doesn't match anything there. But I do want it to delete from EITHER table if there is a match with psor_offenders. Right now, it only deletes if it matches in all three tables.

I spent about 3 hours at the book store tonight reading through some stuff, and it looks like I need to explore outer joins.

valder

1:08 pm on Oct 10, 2005 (gmt 0)

10+ Year Member



My brain is in stand-by atm, so I may be off target..
but will it help to switch the AND to OR in
psor_offenses.id = psor_offenders.id and psor_aliases.id = psor_offenders.id

in the WHERE clause?

58sniper

10:16 pm on Oct 10, 2005 (gmt 0)

10+ Year Member



Actually, I tried that, and it just hung.

Currently, I have two deletes that do the job, I'm just looking for a way to combine them:

mysql_query("DELETE FROM psor_offenses using psor_offenses, psor_offenders WHERE psor_offenses.id = psor_offenders.id and psor_offenders.zip = '$zip'");

mysql_query("DELETE FROM psor_aliases using psor_aliases, psor_offenders WHERE psor_aliases.id = psor_offenders.id and psor_offenders.zip = '$zip'");

txbakers

11:04 pm on Oct 10, 2005 (gmt 0)

WebmasterWorld Senior Member txbakers is a WebmasterWorld Top Contributor of All Time 10+ Year Member



just use two deletes. It's safer that way. With a database you want to be safe.

58sniper

3:08 am on Oct 11, 2005 (gmt 0)

10+ Year Member



I don't mind using the two deletes, except I have them in a loop, that ultimately, will have them run EACH more than 1000 times per day. I'm just trying to optimize things.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month