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]
