Ausgabe
Ich möchte die Haupttabelle (tbl_MN_Omega_Raw) mithilfe einer temporären Tabelle (tbl_MN_Daily_SLA) basierend auf Request_ID aktualisieren. Wenn also die Request_ID aus der temporären Tabelle in der Haupttabelle vorhanden ist, werden die Informationen zu den anderen 19 Spalten ebenfalls aktualisiert, aber ich bin auf a gestoßen Problem, bei dem die gesamte Request_ID aktualisiert wird, aber die Informationen/Details basieren auf den 3 Transaktionen, die ich in meiner temporären Tabelle habe. Hier ist mein Code unten. Danke im Voraus!
Haupttabelle nach Update: [Bildbeschreibung hier eingeben][1]
Temporäre Tabelle: [Bildbeschreibung hier eingeben][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
Lösung
Verwenden Sie nicht die WHERE
Klausel, sondern join
Ihre temporäre in Ihrer Haupttabelle:
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
Beantwortet von – Ryan Wilson
Antwort geprüft von – Mildred Charles (FixError Admin)