Re: SQL-Select and Delete

Douglas Wilson wrote: > > Chengteh Lee wrote: > > > > A friend of mine asked how to use a compound sql to > > delete old records and keep the latest in a table? > > Thanks! Chengteh Lee > > > > example: a table with 2 col. > > col1 col2 > > 1 Kevin (Delete) > > 2 John (Delete) > > 3 Kevin (Delete) > > 4 Kevin (Keep) > > 6 John (Delete) > > 5 John (Keep) > > You'll have to do it in 2 steps, because you cannot > reference the same table in a subquery as you do in > the main query of a delete or update. > > Assuming col1 is a unique key: > > Select col1 from tbl t1 > where col1 < > (select max(col1) > from tbl t1 > where t1.col2=t2.col2) > into temp tmp_tbl> > delete from tbl > where col1 in (select col1 from tmp_tbl)> > Douglas Wilson Don't forget to set your isolation level to "repeatable read" and put all the statements inside one transaction. Stefan