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.:confused:
Printable View
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.:confused:
So what can we do for you dear :rolleyes:
You can find the info by simple googling around.
Anyway check this http://www.arikaplan.com/oracle/ari71998b.html
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
:)
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(*) FROMGROUP 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.