SQL - Delete duplicate pricelist lines

Pay attention:

1) double check always before you delete!

2) No branchID taken into account

Delete duplicate pricelist lines

/*Checking*/
SELECT p.* FROM _etblPriceListPrices p 
 LEFT JOIN (
select  idPriceListPrices, iStockID, iWarehouseID, iPriceListNameID, rank() over (PARTITION BY iStockID, iWarehouseID, iPriceListNameID order by idPriceListPrices) as rnk FROM  _etblPriceListPrices -- order by iStockID, iWarehouseID, iPriceListNameID
) t ON p.IDPriceListPrices = t.IDPriceListPrices
where t.rnk > 1


/*Deleting:*/
delete from _etblPriceListPrices where IDPriceListPrices IN (
 SELECT p.IDPriceListPrices FROM _etblPriceListPrices p 
 LEFT JOIN (
select  idPriceListPrices, iStockID, iWarehouseID, iPriceListNameID, rank() over (PARTITION BY iStockID, iWarehouseID, iPriceListNameID order by idPriceListPrices) as rnk FROM  _etblPriceListPrices -- order by iStockID, iWarehouseID, iPriceListNameID
) t ON p.IDPriceListPrices = t.IDPriceListPrices
where t.rnk > 1)

Space shortcuts

Page tree