| duplicate records [message #39885] |
Wed, 21 August 2002 06:32  |
saravanan
Messages: 70
Registered: October 2000
|
Member |
|
|
i have the following spec
i have a table as follows
JOB_ORDER ITEM_NO PRICE VAL
--------- ---------- ---------- ----------
** 3 4 5 6
2 1 3 4
# 2 3 4
** 3 4 5 6
!! 3 2 1 4
# 1 3 4
3 4 2 1
^^ 1 2 3 4
^^ 1 2 3 4
!! 3 2 1 4
# 1 2 4
in this table there are some records that r either null or duplicated as denoted by some special charaters.
now the requirement is i need to delete these records and write the deleted records into a file/table.
i have written a procedure
/***********************************************************************
procedure to delete duplicate records of jOB_ORDER,ITEM_NO,PRICE,VAL
***********************************************************************/
CREATE OR REPLACE PROCEDURE dup_proc3 IS
CURSOR dup_cur1 is select * from dupcheck;
CURSOR dup_cur is
select job_order,ITEM_NO,PRICE,VAL from dupcheck
group by JOB_ORDER,ITEM_NO,PRICE,VAL having count(*)>1;
dup_rec dup_cur%rowtype; -- record
BEGIN --main
DELETE FROM deletelog; -- emptying the deletelog table.
OPEN dup_cur;
LOOP
FETCH dup_cur INTO dup_rec;
IF (dup_cur%notfound ) THEN -- checks if there are no records in the cursor.
BEGIN
DELETE FROM dupcheck WHERE job_order is NULL or item_no is null or PRICE is null or VAL is;
Exception
When Others Then
DBMS_OUTPUT.PUT_LINE(SQLERRM);
exit;
End;
END IF;
FOR dup_rec1 IN dup_cur1 LOOP -- loop 2
IF ((dup_rec1.item_no=dup_rec.item_no and dup_rec1.job_order=dup_rec.job_order
and dup_rec1.price=dup_rec.price
and dup_rec1.val=dup_rec.val) OR
((dup_rec1.job_order is NULL) or
(dup_rec1.item_no is NULL) or
(dup_rec1.price is NULL) or
(dup_rec1.val is NULL)) ) THEN
BEGIN
DELETE FROM dupcheck WHERE prim_col=dup_rec1.prim_col;
Exception
When Others Then
DBMS_OUTPUT.PUT_LINE(SQLERRM);
exit;
End;
END IF;
END LOOP; -- end loop 2
EXIT WHEN dup_cur%NOTFOUND;
END LOOP; -- end loop 1
close dup_cur;
END; -- end main
/
the problem is iam not able to write to a file/table
please suggest me some method or a still good procedure is most welcome.
sorry for the indentation.
thanks and regards.
|
|
|
|
| Re: duplicate records [message #39887 is a reply to message #39885] |
Wed, 21 August 2002 07:53  |
Rick Cale
Messages: 111
Registered: February 2002
|
Senior Member |
|
|
If you want to move dups to another table you could do
the following.
Of course export table first as a backup.
/* Create a table which contains duplicates only */
CREATE TABLE dups_only AS
SELECT *
FROM original_table a
WHERE a.rowid > (SELECT MIN(b.rowid)
FROM original_table b
WHERE b.job_order = a.job_order
AND b.item_no = a.item_no
AND b.price = a.price
AND b.val = a.val);
/* Delete duplicates from original table */
DELETE FROM original_table a
WHERE a.rowid > (SELECT MIN(b.rowid)
FROM original_table b
WHERE b.job_order = a.job_order
AND b.item_no = a.item_no
AND b.price = a.price
AND b.val = a.val);
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
