|MySQL multi table deletes when data doesn't always exist in all tables|
MySQL mutli table deletes
| 7:06 pm on Oct 8, 2005 (gmt 0)|
I have the following tables:
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.
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.
| 1:20 am on Oct 9, 2005 (gmt 0)|
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.
| 4:39 am on Oct 9, 2005 (gmt 0)|
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.
| 1:08 pm on Oct 10, 2005 (gmt 0)|
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?
| 10:16 pm on Oct 10, 2005 (gmt 0)|
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'");
| 11:04 pm on Oct 10, 2005 (gmt 0)|
just use two deletes. It's safer that way. With a database you want to be safe.
| 3:08 am on Oct 11, 2005 (gmt 0)|
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.