• +86-135-5029-3426
  • sales@vinchin.com
logo

Vinchin Blog

Vinchin Blog Delete/Filter Oracle duplicate data

Delete/Filter Oracle duplicate data

2021-05-27

Create id:

create sequence ac01_id

minvalue 1

maxvalue 9999999

start with 1

increment by 1

cache 20;


Update id

update ac01_temp set id=ac01_id.nextval;


1. Find redundant duplicate records in the table, duplicate records are judged based on a single field (Id)

select * from table where Id in (select Id from table group byId having count(Id)> 1)

 

2. Delete redundant duplicate records in the table. Duplicate records are judged based on a single field (Id), leaving only the record with the smallest rowid

DELETE from table WHERE (id) IN (SELECT id FROM table GROUP BY id HAVING COUNT(id)> 1) AND ROWID NOT IN (SELECT MIN(ROWID) FROM table GROUP BY id HAVING COUNT(*)> 1);

 

3. Find redundant duplicate records in the table (multiple fields)

select * from table a where (a.Id,a.seq) in(select Id,seq from table group by Id,seq having count(*)> 1)

 

4. Delete redundant duplicate records (multiple fields) in the table, leaving only the record with the smallest rowid

delete from table a where (a.Id,a.seq) in (select Id,seq from table group by Id,seq having count(*)> 1) and rowid not in (select min(rowid) from table group by Id ,seq having count(*)>1)

 

5. Find redundant duplicate records (multiple fields) in the table, excluding the record with the smallest rowid

select * from table a where (a.Id,a.seq) in (select Id,seq from table group by Id,seq having count(*)> 1) and rowid not in (select min(rowid) from table group by Id,seq having count(*)>1)


//Update the duplicate data in a table, filter out one of the data,

update ac01_temp t set cf='1' where (

not exists (select id from ac01_temp where cf='2' and aac002 = t.aac002 and id> t.id)) and cf='2'

  • Tag:
  • Trending

Interested Blogs More

DOWNLOAD NOW YOU CAN ENJOY A 60-DAYS FULL-FEATURED FREE TRIAL !