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)
> ....
> ....
> ....
>
> ------------------------------------------------------------
> AcuStaf Corporation Automated Staffing Systems
> 5001 West 80th Street Payroll, Scheduling
> Minneapolis, MN 55437 Time/Attendance ...
> 612-831-4122 ext 248 http://www.acustaf.com
> ------------------------------------------------------------
Hi,
I've seen the solution from "Douglas Wilson". I think it will work.
But if you are going to delete a lot of rows, it's sometimes better
to create a new table instead of manipulating the old one.
begin work;
create newtab ( col1 int, col2 char(8) );
insert into newtab select max(col1), col2 from oldtab group by col2;drop oldtab;
rename table newtab to oldtab;
commit;
If you don't want to remove a lot of rows and if your table is a huge
one, I guess the query with the subquery will take a long time, because
the server has to scan the table twice.
In this case I would create a temporary stored procedure to perform the
delete.
begin work;
create procedure tempproc()
define col1_i, cnt_i int; define col2_vc char(8);
define lastval_vc char(8);
let lastval_vc = null;
let cnt_i = 0;
set isolation to repeatable read;
foreach select col1, col2 into col1_i, col2_vc from table
order by col2
-- , col1 desc { if really the last one should be saved }
if lastval_vc = col2_vc and cnt_i != 0 then
delete from table where col1 = col1_i; elif lastval_vc != col2_vc then
let lastval_vc = col2_vc;
let cnt_i = 1;
end if;
end foreach;
end procedure;
execute procedure tempproc();
drop procedure tempproc;commit;
It's time for lunch now. Have a nice day...
Bye
Stefan