[EMAIL PROTECTED] wrote:
Merlin <[EMAIL PROTECTED]> wrote on 10/04/2005 10:58:21 AM:
> [EMAIL PROTECTED] wrote:
>
> >
> >
> > Merlin <[EMAIL PROTECTED]> 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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
