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

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!

CodePudding user response：

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 :

CodePudding user response：

select pk   
      ,baseproduct  
      ,isprincipal   
from   (
       select *
              ,row_number() over(partition by baseproduct order by isprincipal desc) as rn
       from   t
       ) t
where  rn = 1

Fiddle