delete duplicate records except one

Question

I have a replication of around 200 records in a table, I want to remove all of then except one, how can I do this ??

Have a look on this http://stackoverflow.com/questions/59232/how-do-i-find-duplicate-values-in-a-table-in-oracle. If this is your case kindly close this thread. — Viki888, Nov 24 '16 at 07:12
@viki888, I am asking about deletion not to find duplicates :) — KeenLearner, Nov 24 '16 at 07:13
DELETE FROM Table WHERE (...your select query with limit 199...) — dgk, Nov 24 '16 at 07:14

score 0 · Answer 1 · answered Nov 24 '16 at 07:15

It's easy to introduce duplicate rows of data into Oracle tables by running a data load twice without the primary key or unique indexes created or enabled.Here column1, column2, column3 constitute the identifying key for each record.

DELETE FROM our_table
WHERE rowid not in
(SELECT MIN(rowid)
FROM our_table
GROUP BY column1, column2, column3) ;

score -2 · Answer 2 · answered Nov 24 '16 at 07:16

-2

use the following query. This will be applicable if there is an Id available for the table.

delete from tableA where id in(select top 199 id from tableA)

answered Nov 24 '16 at 07:16

Ajith Menon

42
1

delete duplicate records except one

2 Answers2