Welcome to WebmasterWorld Guest from 220.127.116.11
Forum Moderators: open
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.
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.
I spent about 3 hours at the book store tonight reading through some stuff, and it looks like I need to explore outer joins.
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'");