SQL Server update from table to table-CodePudding

I want to update the Main table (tbl_MN_Omega_Raw) using a Temporary Table (tbl_MN_Daily_SLA) based on Request_ID, so if the Request_ID from Temporary Table exist on the Main Table, the info on the other 19 columns will be updated too but I ran into a problem which all of the Request_ID is being update but the info/details is based on the 3 transactions I have in my Temporary Table. Here's my code below. Thanks in advance!

Main Table after update: [enter image description here][1]

Temporary Table: [enter image description here][2]

UPDATE [tbl_MN_Omega_Raw]
SET  
     Issuing_Country_Name = Y.Issuing_Country_Name
    ,Program_Stucture_Name = Y.Program_Structure
    ,Line_Of_Business_Name = Y.Line_Of_Business_Name
    ,Request_Gross_Premium_Amount = Y.Request_Gross_Premium_Amount
    ,Request_Collection_Currency_Name = Y.Request_Collection_Currency_Name
    ,Client_name = Y.Client_name
    ,RO_Service_Contact = Y.RO_Service_Contact
    ,Request_Effective_Date = Y.Request_Effective_Date
    ,Request_Release_Date = Y.Request_Effective_Date
    ,Original_Request_Release_Date = Y.Original_Request_Release_Date
    ,Request_Type = Y.Request_Type
    ,Request_Status = Y.Request_Status
    ,Request_Reject_Date = Y.Request_Reject_Date
    ,Request_Reject_Reason = Y.Request_Correction_Date
    ,Request_Correction_Date = Y.Request_Accepted_Date
    ,Request_Accepted_Date = Y.Request_Local_Book_Date
    ,Request_Local_Book_Date = Y.Request_Local_Book_Date
    ,Policy_Issued_Date = Y.Policy_Issued_Date
    ,IO_Account_Handler_Contact = Y.IO_Account_Handler_Contact
FROM tbl_MN_Daily_SLA Y
WHERE  EXISTS(SELECT * FROM tbl_MN_Omega_Raw X WHERE X.Request_ID = Y.Request_ID) 


  [1]: https://i.stack.imgur.com/pShmt.png
  [2]: https://i.stack.imgur.com/M0Paz.png

CodePudding user response：

Don't use the WHERE clause, instead join your temporary on your main table:

UPDATE [tbl_MN_Omega_Raw]
SET  
     Issuing_Country_Name = Y.Issuing_Country_Name
    ,Program_Stucture_Name = Y.Program_Structure
    ,Line_Of_Business_Name = Y.Line_Of_Business_Name
    ,Request_Gross_Premium_Amount = Y.Request_Gross_Premium_Amount
    ,Request_Collection_Currency_Name = Y.Request_Collection_Currency_Name
    ,Client_name = Y.Client_name
    ,RO_Service_Contact = Y.RO_Service_Contact
    ,Request_Effective_Date = Y.Request_Effective_Date
    ,Request_Release_Date = Y.Request_Effective_Date
    ,Original_Request_Release_Date = Y.Original_Request_Release_Date
    ,Request_Type = Y.Request_Type
    ,Request_Status = Y.Request_Status
    ,Request_Reject_Date = Y.Request_Reject_Date
    ,Request_Reject_Reason = Y.Request_Correction_Date
    ,Request_Correction_Date = Y.Request_Accepted_Date
    ,Request_Accepted_Date = Y.Request_Local_Book_Date
    ,Request_Local_Book_Date = Y.Request_Local_Book_Date
    ,Policy_Issued_Date = Y.Policy_Issued_Date
    ,IO_Account_Handler_Contact = Y.IO_Account_Handler_Contact
FROM tbl_MN_Daily_SLA Y
inner join tbl_MN_Omega_Raw X 
ON X.Request_ID = Y.Request_ID