From: Peter Brawley
Date: October 4 2005 6:47pm
Subject: Re: deleting unwanted rows
List-Archive: http://lists.mysql.com/mysql/189960
Message-Id: <4342CE58.1040207@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Merlin,

 >I just discovered, that I do have some old rows I do not need anymore.
 >A result from forgeting to empty the table before starting to go into 
production :-)
 >However, I do try to find the rows which are not asociated with 
another table in
 >order to delete them. I have 2 tables: table1 and table2

 >table1 has the key: "ID"
 >table2 has the subkey "table1_id"

To retrieve table1 rows for which there is no table2 row with a 
table1_id value matching a table1.id value, you want a join netween the 
two tables where table2.table1_id is null, something like...

  SELECT id
  FROM table1
  INNER JOIN table2 ON table1.id=table2.table1_id
  WHERE table2.table1_id IS NULL;

PB

-----


Merlin wrote:

> Hi there,
>
> I just discovered, that I do have some old rows I do not need anymore. 
> A result from forgeting to empty the table before starting to go into 
> production :-)
> However, I do try to find the rows which are not asociated with 
> another table in order to delete them.
> I have 2 tables:
> table1 and table2
>
> table1 has the key: "ID"
> table2 has the subkey "table1_id"
>
> Now I would like to delete all rows in table1 which are not listed in 
> table2 with an id.
> I tried:
> SELECT
>    t1.*
> FROM
>    table1 as t1,
>    table2 as t2
> WHERE
>    t1.ID != t2.table1_id
>
> But this returns hundreds of thousends of results.
> I also tryed to group by t1.ID, but it did not help
>
> Does anybody have a good idea how to get rid of those rows?
>
> Thanx, Merlin
>
> PS: Thanx for the answer for the question with full text search! That 
> worked excellent!
>


-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.9/118 - Release Date: 10/3/2005