USE T201819




CREATE TABLE Tmp_BROKERAGE_DETAIL (BrokSrNo Varchar(255),BrokCompCode Varchar(255),PaySrNo Varchar(255), PayCompCode Varchar(255), PaySuppCode Varchar(255))

INSERT INTO Tmp_BROKERAGE_DETAIL SELECT BROKERAGE_ADDLESS_DETAIL.BrokSrNo, BROKERAGE_ADDLESS_DETAIL.CompCode, RECEIVED_DETAIL.RecSrNo, RECEIVED_DETAIL.RecFirmCode, RECEIVED_DETAIL.RecSuppCode FROM BROKERAGE_DETAIL INNER JOIN BROKERAGE_ADDLESS_DETAIL ON BROKERAGE_DETAIL.BrokSrNo = BROKERAGE_ADDLESS_DETAIL.BrokSrNo AND BROKERAGE_DETAIL.CompCode = BROKERAGE_ADDLESS_DETAIL.CompCode INNER JOIN RECEIVED_DETAIL ON BROKERAGE_ADDLESS_DETAIL.PaySrNo = RECEIVED_DETAIL.RecSrNo

UPDATE BROKERAGE_ADDLESS_DETAIL Set CompCode = T1.F1 FROM (SELECT PayCompCode AS F1, BrokSrNo AS F2, BrokCompCode AS F3 FROM Tmp_BROKERAGE_DETAIL) AS T1 WHERE T1.F2=BrokSrNo AND T1.F3 = CompCode
UPDATE BROKERAGE_DETAIL Set CompCode = T1.F1, BrokSuppCode = T1.F4 FROM (SELECT PayCompCode AS F1, BrokSrNo AS F2, BrokCompCode AS F3, PaySuppCode AS F4 FROM Tmp_BROKERAGE_DETAIL) AS T1 WHERE T1.F2=BrokSrNo AND T1.F3 = CompCode


drop table Tmp_BROKERAGE_DETAIL