oracle

Any idea,
I accidentally added records twice in a oracle table.
Any idea how can I remove duplicate records in a table easily?
Thanks.
Al
ALad2005Asked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
null values will cause a problem.

Try this:
delete from tab1 t1 where rowid > (
 select min(rowid) from tab1 t2 where
      (t1.col1=t2.col1 or (t1.col1 is null and t2.col1 is null))
      and
      (t1.col2=t2.col2 or (t1.col2 is null and t2.col2 is null))
 )
0
 
slightwv (䄆 Netminder) Commented:
If it doesn't matter which one you keep, the correlated subquery is a good way:
delete from tab1 t1 where rowid > (
select min(rowid) from tab1 t2 where t1.col1=t2.col1 and t1.col2=t2.col2
)


There are several others.
0
 
Anil_LadCommented:
I used this, but no rows were deleted.
some of records have empty values , may be the compare might fail there?
Thanks.
Anil
0
 
awking00Commented:
Is there a key field (or key fields) that make a record unique? If so,
delete from tab1 where rowid in
(select rowid from (select rowid, row_number() over (partition by key, order by key) rn from tab1)
 where rn > 1;
0
 
ALad2005Author Commented:
thank you guys.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.