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

> SGreen@stripped wrote:
> 
> >
> >
> > 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 
> 
> Hi,
> 
> this workes excellent, but I tried to replace the "select * from" with 
> delete from,
> but this did not work. Do I have to use a differnt syntax for deleting 
> in this case?
> 
> Thanx, Merlin

Yes, and it which form you can use depends on your server's version. More 
details here:

http://dev.mysql.com/doc/mysql/en/delete.html

DELETE table1
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

PS - always CC: the list on all responses
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