⬅ Back UPDATE MILL SEND SRNO AS PER ITEM NAME AND ITEM MTS.sql
use t27v001
---------------------------- UPDATE MILL SEND SRNO AS PER ITEM NAME AND ITEM MTS
select * into TmpPurSrNoData FROM (SELECT GREY_SEND_TO_MILL_TAKA_DETAIL.SrNo, GREY_SEND_TO_MILL_TAKA_DETAIL.PurSrNo, GREY_SEND_TO_MILL.ItemName, GREY_SEND_TO_MILL.ItemMts FROM GREY_SEND_TO_MILL_TAKA_DETAIL INNER JOIN GREY_SEND_TO_MILL ON GREY_SEND_TO_MILL.SrNo = GREY_SEND_TO_MILL_TAKA_DETAIL.Srno WHERE LEN(GREY_SEND_TO_MILL_TAKA_DETAIL.PurSrNo)=5 ) as t1
----------------------------
ALTER TABLE TmpPurSrNoData ADD NewSrNo Varchar(255)
UPDATE TmpPurSrNoData set NewSrNo = PurSrNo
UPDATE TmpPurSrNoData set NewSrNo = T1.F1 FROM (SELECT SrNo AS F1, ItemName AS F2, ItemMts AS F3 FROM OPENING_GREY_SEND_TO_MILL) AS T1 WHERE ItemMts = T1.F3 AND ItemName=T1.F2 
update GREY_SEND_TO_MILL_TAKA_DETAIL set PurSrNo = T1.F2 FROM (SELECT srno as f1, newsrno as f2, pursrno as f3 from TmpPurSrNoData) as t1 where t1.f1 = srno and t1.f3 = PurSrNo
----------------------------
drop table TmpPurSrNoData