Tuesday, October 9, 2012

Delete Duplicate Rows from Multiple Tables

There are various ways for removing duplicate rows from  table. In my scenario there are three table which from where I have to delete duplicate rows. All these tables have relation. These tables are-:

Purchase_Rcv -: Keeps information about each purchase receive.
Purchase_RcvDet -: Keeps information about purchased product  for each purchase receive
BarcodeDet -: Keeps Barcode Detail for each received product
There is one more table that is -:
POS_STOCK-: As its name exhibits, keeps stock information

So I have to also update stock in this table.

So, to implement this I wrote a select statement which fetches all the rows of table without duplicacy and the query is :
SELECT min(PoRcvDet_Id) as id   FROM [Purchase].[vw_PurchaseRcvDet] group by [Product_code]

Here Product_Code is the column on which I want to remove duplicacy. So, I used group by on Product_Code which returns distinct rows based on Product_Code.

I used min() to keep first entry.One can use max() also.

 Then I wrote the Delete Command which deletes all rows which are not in the selected rows.

Delete FROM [Purchase].[BarcodeDet]
      WHERE fkPoRcvDetId not in
      (SELECT min(PoRcvDet_Id) as id   FROM [Purchase].[vw_PurchaseRcvDet] group by [Product_code] )
Delete FROM Purchase.Purchase_RcvDet
WHERE PoRcvDet_Id not in
      (SELECT min(PoRcvDet_Id) as id  FROM [Purchase].[vw_PurchaseRcvDet] group by [Product_code] )
Delete FROM Purchase.Purchase_Rcv
WHERE PoRcv_Id not in
      (SELECT min(fkPoRcv_Id) as id  FROM [Purchase].[vw_PurchaseRcvDet] group by [Product_code] )

Update Stock--:

   SET [Stock] = b.[Qty]
  from [Purchase].[Purchase_RcvDet] as b
  left outer join  [dbo].[POS_STOCK] on b.[fkProductId]=[dbo].[POS_STOCK].[fkProductId]

In my scenario POS_STOCK has same no of rows as in Purchase_RcvDet. Thats why I have used  left outer join.

No comments:

Post a Comment