List:General Discussion« Previous MessageNext Message »
From:Merlin Date:October 5 2005 8:35am
Subject:Re: deleting unwanted rows
View as plain text  
SGreen@stripped wrote:

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

Thanx that worked excellent! Now I know why this has happened. I have 
forgot to delete entries in table1 which refer to table 2.
Is there a way to delete them with one statement, or do I have to make a 
select to get the table1_id first and then do 2 delets?

I do have at the moment 3 querys!:
    # get table1_id
    SELECT table1_id
    from ...
    WHERE ID = ...

    # delete dependent entry
    DELETE
        FROM $DB.$T5
    WHERE
        ID = '$data[table1_id]'
    LIMIT 1

    # delete main entry
    DELETE
        FROM $DB.$T4
    WHERE
        ID = '$data[id]'
    LIMIT 1

Is there a better solution as this?

Thanx, Merlin
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