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]
