At 8:39 -0800 3/4/03, Joel Fentin wrote:
>I am working on a relational database where an ID value in one table
>should have one or more records with the same ID value in another table.
>I want to snuff any record where an ID exists in one table but not in the
>other.
>
>On page 165 of the 2000 version of the DuBois book there is an instruction
>that will at least identify such orphans. But I can't even get that program
>to work for me. The machine hangs up. There is no instruction for deleting
>them.
>
>I think my problem is that the table with the orphans has so many records
>that the instruction can't be executed. Even if I add Distinct & Limit 10, I
>still can't get it to work.
>
>Here is the instruction I am using:
>
>select distinct disco.NoCinta from disco left join titulo on disco.NoCinta =
>titulo.NoCinta where titulo.NoCinta is null limit 10;
>
>Any help appreciated.
The instruction for deleting them is on page 235 of the 2003 DuBois book. :-)
DELETE disco FROM disco LEFT JOIN titulo ON disco.NoCinta =
titulo.NoCinta WHERE titulo.NoCinta IS NULL;
This requires MySQL 4.
Not sure why your SELECT isn't working. It looks legal. How big are your
tables?
>--
>Joel Fentin tel: 760-749-8863 FAX: 760-749-8864
>email: joel@stripped
>Biz: http://fentin.com
>Personal: http://fentin.com/me/
