CODEWITHSUNDEEP
sqldb2
Vicky
Vicky

Reputation: 17375

Removing duplicates from a table in DB2

I have a table TAB1 in DB2 as below:

ID              Status
_______________________
10              Open
8               Closed
7               Open
10              Closed
7               Open
9               Open
11              Open
5               Closed
_______________________

I want to remove duplicates from above table in a single sql query based on the following business rule:

1) If an ID is duplicated, remove the one with Status Closed. e.g. the entry 10 with Closed should be removed.

2) If an ID is duplicated and the Status is also same, all the duplicate entries should be removed. e.g. both the entries for ID 7 with Status Open should be deleted.

How can I achieve this in a single Delete query ?

Thanks for reading!

Upvotes: 0

Views: 338

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I think the following is almost a direct translation of your requirements:

delete from tab1
    where (tab1.status = 'closed' and
           exists (select 1
                   from tab1 t2
                   where t2.id = tab1.id and t2.status <> 'closed'
                  )
          ) or
          (select count(*)
           from tab1 t2
           where t2.id = tab1.id and t2.status = tab1.status
          ) > 1;

Upvotes: 1

Related Questions