homepage Welcome to WebmasterWorld Guest from 54.197.215.146
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

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




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

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




msg:1580351
 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.

58sniper




msg:1580352
 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.

valder




msg:1580353
 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?

58sniper




msg:1580354
 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'");

txbakers




msg:1580355
 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.

58sniper




msg:1580356
 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved