How to delete duplicates by a column by condition

Nicolas Villacorta 0
Viewed 33

I'm having the next example table as a result of an UNION.

pk baseProduct isPrincipal
1 10 1
2 11 1
3 12 0
4 13 0
5 11 0
6 15 0
7 29 0

Some rows with the 'isPrincipal' on true/1 are repeated by same baseProduct, there is another pk product, but they have the same baseProduct.

I want to delete the duplicated baseProducts rows maintaining the isPrincipal pk.

Example; PK 2 and 5 have the same baseProduct but one of them have the isPrincipal flag, then I want to delete the row with pk 5 that doesn't have the flag. Is there is no a baseProduct with the isPrincipal flag I want to remain that entry, for example, at this table the PK 5 is the only one I want to remove.

I got this table after an UNION

SELECT ( ..... )tbl

Can you help me to filter this? My SQL knowledge is pretty low. Thanks!

sql
2 Answers

This should help. It groups and counts the duplicate rows using the sub query and uses that as the criteria to identity the correct duplicate to delete. This leaves the record with the isPrincipal in tact.

delete principal

from principal
left join
(
    select baseproduct, count(baseproduct) as NoOfProducts
    from principal 
    group by baseproduct

) base
on
#principal.baseproduct = base.baseproduct
where base.NoOfProducts = 2 and #principle.isprincipal = 0

The result is :

id baseproduct isprincipal NoOfProducts
1 10 1 1
2 11 1 1
3 12 0 1
4 13 0 1
6 15 0 1
7 29 0 1
i_coder0 
select pk   
      ,baseproduct  
      ,isprincipal   
from   (
       select *
              ,row_number() over(partition by baseproduct order by isprincipal desc) as rn
       from   t
       ) t
where  rn = 1
pk baseproduct isprincipal
1 10 1
2 11 1
3 12 0
4 13 0
6 15 0
7 29 0

Fiddle

DannySlor0
Related