List:MySQL on Win32« Previous MessageNext Message »
From:Paul DuBois Date:March 4 2003 11:26pm
Subject:Re: weeding out orphans
View as plain text  
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/

Thread
weeding out orphansJoel Fentin4 Mar
  • Re: weeding out orphansArley Carter4 Mar
  • Re: weeding out orphansPetr Vileta5 Mar
  • Re: weeding out orphansPaul DuBois5 Mar
  • Re: weeding out orphansJoel Fentin5 Mar
    • Re: weeding out orphansPaul DuBois5 Mar
RE: weeding out orphansjbonnett7 Mar