Geek asks and answers

How to remove duplicate rows from an Oracle database?

Possible duplicate:
Oracle Delete rows matching multiple values

We have a table in which the same data has been inserted twice as most (but not all) of the rows that appear twice in the table. Simply put, I would like the SQL statement to delete one version of a row while preserving another; I do not mind which version will be removed, as they are identical.

The table structure looks something like this:

FID, unique_ID, COL3, COL4 ....

Unique_ID- This is the primary key, each of which appears only once. FIDis a key that is unique to each function, so if it appears more than once, duplicates must be deleted.

To select objects with duplicates, follow these steps:

select count(*) from TABLE GROUP by FID

Unfortunately, I cannot figure out how to go from this to the SQL delete statement, which will delete extraneous rows, leaving only one of them.

This question was asked before, and I tried to create a table with excellent, but how can I get all the columns without naming them? This gets only one column FID and lists all the columns that need to be saved:ORA-00936: missing expression

CREATE TABLE secondtable NOLOGGING as select distinct FID from TABLE

+3
source share
5 answers

If you don't care which row is saved

DELETE FROM your_table_name a
 WHERE EXISTS( SELECT 1
                 FROM your_table_name b
                WHERE a.fid = b.fid
                  AND a.unique_id < b.unique_id )

After that, you will want to add a constraint to the table, which guarantees uniqueness FID.

+6
source

try it

DELETE FROM table_name A WHERE ROWID > (
SELECT min(rowid) FROM table_name B
WHERE A.FID = B.FID)
+3
source 

DELETE FROM x WHERE ROWID IN
(WITH y AS (SELECT xCOL, MIN(ROWID) FROM x GROUP BY xCOL HAVING COUNT(xCOL) > 1)
SELCT a.ROWID FROM x, y WHERE x.XCOL=y.XCOL and x.ROWIDy.ROWID)
+1

.

DELETE FROM firsttable WHERE unique_ID NOT IN 
(SELECT MAX(unique_ID) FROM firsttable GROUP BY FID)

EDIT: :

SELECT MAX(unique_ID) FROM firsttable GROUP BY FID;

sql unique_ID . delete unique_ID .

0
source

You can try this.

delete from tablename a
where a.logid, a.pointid, a.routeid) in (select logid, pointid, routeid from tablename 
group by logid, pointid, routeid having count(*) > 1)
and rowid not in (select min(rowid) from tablename
group by logid, pointid, routeid having count(*) > 1)
0
source

More articles:


All Articles