Nevermind... Just read an interesting comment in the MySQL online docs.
http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html First user comment pointed me in the right direction. Thanks for your help though. Much appreciated! (Especially when I upgrade to 4.x) -- Greg On Tue, Jun 24, 2003 at 11:50:01AM -0500, Greg Klaus wrote: > Jake, > > Since I am running MySQL 3.23.5x, I cannot do subselects. > > I am able to do the following: > > SELECT * FROM Items_Pictures > LEFT JOIN Items > ON Items_Pictures.Items_ID = Items.Items_ID > WHERE Items.Items_ID IS NULL; > > *BUT*, if I try to do a DELETE instead > > DELETE FROM Items_Pictures > LEFT JOIN Items > ON Items_Pictures.Items_ID = Items.Items_ID > WHERE Items.Items_ID IS NULL; > > I get: > ERROR 1064: You have an error in your SQL syntax near 'LEFT JOIN Items > ON Items_Pictures.Items_ID = Items.Items_ID WHERE Items.Items_ID' > > I have a couple of databases I need to clean up this way manually. > > Any help appreciated.. > > On Wed, Jun 18, 2003 at 10:10:57PM -0700, Jake Johnson wrote: > > Hello Greg, > > You are much better off using a not exists clause... > > > > delete from child c > > where not exists ( select 1 from parent p > > where p.id = c.id) > > > > > > > > Regards, > > Jake Johnson > > [EMAIL PROTECTED] > > > > ------------------------------------------------------------------ > > Plutoid - http://www.plutoid.com > > Shop Plutoid for the best prices on Rims and Car Audio Products > > > > > > On Wed, 18 Jun 2003, Greg Klaus wrote: > > > > > I am trying to delete some orphaned records in an old database on a > > > website that I've recently taken over. Although the website is php > > > driven, I am doing this manually in a mysql client. > > > > > > Mysql 3.23.54 > > > > > > Tables: > > > > > > Items: > > > Items_ID > > > .... > > > > > > Pictures: > > > Picture_ID > > > Items_ID > > > .... > > > > > > I want to get rid of any entries in Pictures that are orphaned (No > > > Items_ID in Items) > > > > > > Here is the query I'm trying to do, which I thought was correct, > > > according to my surfing around google. > > > > > > DELETE FROM Pictures > > > WHERE Picture_ID IN > > > ( > > > SELECT Pictures.Picture_ID FROM Pictures > > > LEFT JOIN Items using (Items_ID) > > > WHERE Items_ID IS NULL > > > ) > > > > > > I also may have to do this in a 3 table scheme as well where the Cat_ID > > > is gone and there are orphaned Items, which in turn means orphaned > > > Pictures. > > > > > > Cats: > > > Cat_ID > > > .... > > > > > > Items: > > > Items_ID > > > Cat_ID > > > .... > > > > > > Pictures: > > > Picture_ID > > > Items_ID > > > .... > > > > > > Any help would be appreciated. > > > > > > Am I left to create a php script to do the cleaning for me or delete > > > items individually? > > > > > > -- > > > Greg > > > Nec Mors, Nec Requies. Carpe Noctum! > > > > > > -- > > > MySQL General Mailing List > > > For list archives: http://lists.mysql.com/mysql > > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- > Greg Klaus - [EMAIL PROTECTED] > ------------------------------------- > TIMEatics > web: http://www.timeatics.com > voice: 785.456.7600 > fax: 785.456.7601 > aim: TIME Greg Klaus > -=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=- > Nec Mors, Nec Requies. Carpe Noctum! > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Greg Klaus - [EMAIL PROTECTED] ------------------------------------- TIMEatics web: http://www.timeatics.com voice: 785.456.7600 fax: 785.456.7601 aim: TIME Greg Klaus -=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=- Nec Mors, Nec Requies. Carpe Noctum! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
