List:General Discussion« Previous MessageNext Message »
From:SGreen Date:October 4 2005 2:30pm
Subject:Re: deleting unwanted rows
View as plain text  
Merlin <ngroups@stripped> wrote on 10/04/2005 10:21:00 AM:

> 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!
> 

Use a LEFT JOIN not an INNER JOIN!

SELECT 
        t1.*
FROM table1 t1
LEFT JOIN table2 t2
        ON t1.id = t2.table1_id
WHERE t2.table1_id is NULL;

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Thread
deleting unwanted rowsMerlin4 Oct
  • Re: deleting unwanted rowsSGreen4 Oct
  • Re: deleting unwanted rowsPeter Brawley4 Oct
  • Re: deleting unwanted rowsPeter Brawley4 Oct
Re: deleting unwanted rowsSGreen4 Oct
  • Re: deleting unwanted rowsMerlin5 Oct