Re: SQL-Select and Delete
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