(Oracle) Remove duplicate record


This oracle query show you trick of deleting duplicate record on Oracle Database

DELETE FROM oracle_table
WHERE ROWID IN
(
SELECT e.ROWID
FROM oracle_table e
WHERE e.no_transaksi IN
(
select
no_transaksi
from
oracle_table
where tgl_cmplt BETWEEN TO_Date(’01-05-2012′,’DD-MM-YYYY’) AND TO_Date(’29-05-2012′, ‘DD-MM-YYYY’)
and status=’70’
group by no_transaksi
having count(com) >1
)
MINUS
select
min(rowid)
from
oracle_table
where tgl_cmplt BETWEEN TO_Date(’01-05-2012′,’DD-MM-YYYY’) AND TO_Date(’29-05-2012′, ‘DD-MM-YYYY’)
and status=’70’
group by no_transaksi
having count(com) >1
)

About blackphp

All about programming php, ajax, jquery, mysql, etc, tips n trick. Now, i'm concern in php programming with codeigniter framework n also training my design skill with photoshop or corel... wish me luck! ('o')

Posted on August 28, 2014, in Oracle and tagged , , , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: