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
