deleting duplicate data from a table

[k_krajpoot]

hi gud morning to all
i have accidently copied same data three times in a table when i have disabled my primary key constraints, now nither i am able to enable my primary key constraints nor able to delete the duplicate records.

[gtcol]

So what can we do for you dear

[malay_biswal]

You can find the info by simple googling around.

Anyway check this http://www.arikaplan.com/oracle/ari71998b.html

[crimpe]

Hi,
This happened to me some days ago,and i had to write up a script like this;

DELETE FROM table
WHERE rowid not in(
SELECT MIN(rowid)
FROM emp3
GROUP BY col1,col2,col3
Please ensure that all your columns appear in the group by clause!

The rowid is a unique field within every oracle table.
The subquery selects the smallest instance of each record(i.e ,1 out of every 3 instances of your record).
The main query will delete 2 out of the 3 records returned by the subquery.
Hope this helps

[Manis]

DELETE FROM
WHERE (unique-row-identifier-columns) IN (
SELECT unique-row-identifier-columns FROM
GROUP BY unique-row-identifier-columns HAVING COUNT(*) > 1 AND
ROWNUM < (SELECT COUNT(*) FROM GROUP BY
unique-row-identifier-columns HAVING COUNT(*) > 1;

unique-row-identifier-columns -ARE COLUMNS WHICH MAKES A RECORD KEY BASED ON WHICH YOU ARE QUERING THE TABLE AND WHICH ARE DUPLICATE IN THE TABLE.