-
duplicate rows
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
e.g
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!
which is
A B C
5 123
5 124
6 567
7 568
How do I do this?
Thanks
-
... 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
-
Problem resolved
Many Thanks.
If a man is sufficiently unimaginative to produce evidence in support of a lie, he might just as well speak the truth at once.
Oscar Wilde
--The Decay of Lying