Hi, I have a table which has duplicate rows and I want to put a primary key on two of the columns say col A and B (composite key). The problem I have is I can remove duplicate rows with the script below from col A
delete from &&table_name t1
where t1.&&column_name in (select &&column_name,
from &&table_name t2
where t1.rowid < t2.rowid
and t1.&&column_name = t2.&&column_name)
This will remove all duplicates from col A however there is some data
in col B which is needed and when combined with col A make it unique
see the example below. when I run the above remove script. It removes one of the 5 entries which is not what I want!
A B C
How do I do this?
... where (t1.a, t1.b) in (select a,b from ...)
delete from t1 a where a.rowid in (select a.rowid from t1 b where a.a=b.a and a.b=b.b and a.rowid
Oracle Certified Master - September, 2003, the Second OCM in China
*** LOOKING for PART TIME JOB***
Data Warehouse & Business Intelligence Expert
MCSE, CCNA, SCJP, SCSA from 1998
If a man is sufficiently unimaginative to produce evidence in support of a lie, he might just as well speak the truth at once.
--The Decay of Lying
Click Here to Expand Forum to Full Width