deleting duplicate records in a table

Posted: July 29, 2008 in Database
Tags:

Banyangin klo ternyata yang duplicate ada ratusan bahkan juta-an records, klo cuman 1-2 sih enteng bisa hapusin manual satu-satu.

Klo pake software TOAD terbaru sih enak ada fasilitas delete multiple duplicate data. Caranya pilih menu database -> compare -> duplicate data. Trus pencet ctrl+A — klik kanan — deleted all selected rows, tapi jangan keburu eksekusi edit dulu querynya tanda “<>” diganti “>” biar record yang double ngga semua didelete tapi disisain satu record saja.

Nah bagi yang ngga punya softwarenya yah terpaksa pake script. Setelah coba-coba akhirnya ketemu juga scriptnya:

DELETE FROM
table_name A
WHERE
a.rowid >
ANY (
SELECT
B.rowid
FROM
table_name B
WHERE
A.col1 = B.col1
AND
A.col2 = B.col2
);

—atau pake cara ini untuk hapus duplicate records–
DELETE
FROM table_name
WHERE rowid not in
(SELECT MIN(rowid) –bisa pake min ato max–
FROM table_name
GROUP BY column1, column2, column3… )

mudah-mudahan bermanfaat yah..🙂

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