Re: Removing duplicated rows revisited - I'm feeln' mighty lame

The code below has a small error in it: The semicolon at the beginning of the line

; IF delete_commit >= ...

should be deleted...

On Thu, 18 Mar 1999 15:40:58 GMT, James <james_lorenzen_at_allianzlife.com> wrote:

>DECLARE CURSOR dup_csr IS SELECT person_id , date_called , origin_phone ,
> destination_phone , duration_minutes , duration_seconds ,
>MIN(date_posted) AS date_posted FROM call_table GROUP BY person_id,
>date_called, origin_phone, destination_phone , duration_minutes,
>duration_seconds HAVING Count(*)>1 ; delete_cnt NUMBER := 0 ;
>delete_commit NUMBER := 0 ; BEGIN DBMS_OUTPUT.PUT_LINE('Start at ' ||
>TO_CHAR(SYSDATE,'hh24:mi:ss')) ; FOR dup_rec IN dup_csr LOOP DELETE FROM
>call_table WHERE person_id = duprec.person_id AND date_called =
>duprec.date_called AND origin_phone = duprec.origin_phone AND
>destination_phone = duprec.destination_phone AND duration_minutes =
>duprec.duration_minutes AND duration_seconds = duprec.duration_seconds AND
>date_posted <> duprec.date_posted; delete_cnt := delete_cnt + SQL%ROWCOUNT
>; -- this is the rows deleted delete_commit := delete_commit + SQL%ROWCOUNT
>; IF delete_commit >= 10000 THEN COMMIT ; DBMS_OUTPUT.PUT_LINE('Commit
>taken at ' || TO_CHAR(SYSDATE,'hh24:mi:ss') || ' rows = ' || delete_commit
>|| ' so far ' || delete_cnt || ' rows deleted') ; delete_commit := 0 ; END
>IF ; END LOOP ; DBMS_OUTPUT.PUT_LINE('End at' ||
>TO_CHAR(SYSDATE,'hh24:mi:ss') || ' ' || delete_cnt || ' rows deleted') ;
>END ;
Received on Thu Mar 18 1999 - 10:43:28 CST