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
