CODEWITHSUNDEEP
sqloracle-database
CodeGeek123
CodeGeek123

Reputation: 4501

Deleting Semi Duplicate Rows in ORACLE SQL

I have rows in my table that needs deleting based on a few columns being duplicates.

e.g Col1,Col2,Col3,Col4

If Col1,Col2 and Col3 are duplicates regardless of what value is in Col4 I want both these duplicates deleted. How do I do this?

Upvotes: 1

Views: 199

Answers (3)

jarlh
jarlh

Reputation: 44766

Use EXISTS to remove a row if another row with same col1, col2 and col3 exists with a lower col4 value. I.e keep one col1, col2, col3 row.

delete from tablename t1
where exists (select 1 from tablename t2
              where t2.col1 = t1.col1
                and t2.col2 = t1.col2
                and t2.col3 = t1.col3
                and t2.col4 < t1.col4)

To remove both/all rows, skip the col4 condition, do a group by instead:

delete from tablename t1
where exists (select 1 from tablename t2
              where t2.col1 = t1.col1
                and t2.col2 = t1.col2
                and t2.col3 = t1.col3
              group by t2.col1, t2.col2, t2.col3
              having count(*) > 1)

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

Group by these IDs and check with HAVING whether there are duplicates. With the duplicates thus found delete the records.

delete from mytable 
where  (col1,col2,col3) in
(
  select col1,col2,col3
  from mytable 
  group by col1,col2,col3
  having count(*) > 1
);

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You can do this using the where clause:

delete from t
    where (col1, col2, col3) in (select col1, col2, col3
                                 from t
                                 group by col1, col2, col3
                                 having count(*) > 1
                                );

Upvotes: 3

Related Questions