Duplicate data

[ddrillich]

Good Day,

I have a table with about 40,000 rows and I’m trying to find duplicate rows – rows in which 5 columns (that should have been the primary key) are identical.

I tried the following query:

select a,b,c,d,e
from
tableA

minus

select distinct a,b,c,d,e
from
tableA

To my surprise, it returned no rows even though I know that there are over 100 rows with these identical columns.

Any ideas?

Thanks,
Dan

 

[R1972]

Try this

select a,b,c,d,e
from
tableA
group by a,b,c,d,e having count(*) > 1

 

[SantaMufasa]

Once you use R1972's fine script to identify the duplicate values for a,b,c,d,and e, I'll bet you'll want to eventually get rid of all but one of the duplicate rows, right?

First, (if you hate risk as much as I do) you'll want to review the rows subject to deletion. Here is a script to review all the duplicate rows (except the row in each set of dups having the highest rowid):

select rowid,a,b,c,d,e
from TableA outer
where rowid < (select max(rowid)
from TableA inner
where (inner.a = outer.a
or (inner.a is null
and outer.a is null))
and (inner.b = outer.b
or (inner.b is null
and outer.b is null))
and (inner.c = outer.c
or (inner.c is null
and outer.c is null))
and (inner.d = outer.d
or (inner.d is null
and outer.d is null))
and (inner.e = outer.e
or (inner.e is null
and outer.e is null))
)
/

Then, once you review and approve of the rows to delete, this script will delete all the duplicate rows except the one with the highest rowid among the duplicates:

delete from TableA where rowid in (
select rowid
from TableA outer
where rowid < (select max(rowid)
from TableA inner
where (inner.a = outer.a
or (inner.a is null
and outer.a is null))
and (inner.b = outer.b
or (inner.b is null
and outer.b is null))
and (inner.c = outer.c
or (inner.c is null
and outer.c is null))
and (inner.d = outer.d
or (inner.d is null
and outer.d is null))
and (inner.e = outer.e
or (inner.e is null
and outer.e is null))
) )
/

Let me know if this takes care of business for you.

Dave