use T24R001

CREATE TABLE New_GreyPurchase (OldSrNo Varchar(255),NewSrNo Varchar(255))
INSERT INTO New_GreyPurchase select SrNo, right('000000'+Convert(Varchar(6),Convert(Numeric(6),SrNo)-2597),6) from grey_purchase where SrNo >= '001414'

SELECT * INTO Tmp_GREY_PURCHASE FROM GREY_PURCHASE where SrNo >= '001414'
SELECT * INTO Tmp_GREY_PURCHASE_ITEM_DETAIL FROM GREY_PURCHASE_ITEM_DETAIL where SrNo >= '001414'
SELECT * INTO Tmp_GREY_PURCHASE_ITEM_TAKA_DETAIL FROM GREY_PURCHASE_ITEM_TAKA_DETAIL where SrNo >= '001414'
SELECT * INTO Tmp_GREY_PURCHASE_YARN_TAKA_DETAIL FROM GREY_PURCHASE_YARN_TAKA_DETAIL where SrNo >= '001414'

DELETE FROM GREY_PURCHASE_ITEM_DETAIL where SrNo >= '001414'
DELETE FROM GREY_PURCHASE_ITEM_TAKA_DETAIL where SrNo >= '001414'
DELETE FROM GREY_PURCHASE_YARN_TAKA_DETAIL where SrNo >= '001414'    
DELETE FROM GREY_PURCHASE where SrNo >= '001414'

UPDATE Tmp_GREY_PURCHASE SET SrNo = T1.F2 FROM (SELECT OldSrNo AS F1, NewSrNo AS F2 FROM New_GreyPurchase) AS T1 WHERE T1.F1 COLLATE DATABASE_DEFAULT= SrNo
UPDATE Tmp_GREY_PURCHASE_ITEM_DETAIL SET SrNo = T1.F2 FROM (SELECT OldSrNo AS F1, NewSrNo AS F2 FROM New_GreyPurchase) AS T1 WHERE T1.F1 COLLATE DATABASE_DEFAULT= SrNo
UPDATE Tmp_GREY_PURCHASE_ITEM_TAKA_DETAIL SET SrNo = T1.F2 FROM (SELECT OldSrNo AS F1, NewSrNo AS F2 FROM New_GreyPurchase) AS T1 WHERE T1.F1 COLLATE DATABASE_DEFAULT= SrNo
UPDATE Tmp_GREY_PURCHASE_YARN_TAKA_DETAIL SET SrNo = T1.F2 FROM (SELECT OldSrNo AS F1, NewSrNo AS F2 FROM New_GreyPurchase) AS T1 WHERE T1.F1 COLLATE DATABASE_DEFAULT= SrNo

INSERT INTO GREY_PURCHASE SELECT * FROM Tmp_GREY_PURCHASE
INSERT INTO GREY_PURCHASE_ITEM_DETAIL SELECT * FROM Tmp_GREY_PURCHASE_ITEM_DETAIL
INSERT INTO GREY_PURCHASE_ITEM_TAKA_DETAIL SELECT * FROM Tmp_GREY_PURCHASE_ITEM_TAKA_DETAIL
INSERT INTO GREY_PURCHASE_YARN_TAKA_DETAIL SELECT * FROM Tmp_GREY_PURCHASE_YARN_TAKA_DETAIL

UPDATE GREY_SEND_TO_MILL_TAKA_DETAIL SET PurSrNo = T1.F2 FROM (SELECT OldSrNo AS F1, NewSrNo AS F2 FROM New_GreyPurchase) AS T1 WHERE T1.F1 COLLATE DATABASE_DEFAULT= PurSrNo