How to Update One SQL Table from Another Using a Matching ID?

Updating SQL Tables Based on Matching IDs

This guide demonstrates how to update records in one SQL table (Sales_Import) using corresponding data from another table (RetrieveAccountNumber), matching on a common LeadID. A common challenge is updating AccountNumber in Sales_Import using values from RetrieveAccountNumber without encountering null values. The solution utilizes an UPDATE statement with a JOIN.

Efficiently Updating Records with JOINs

The UPDATE ... FROM ... JOIN syntax allows for efficient updates by combining data from multiple tables. This method ensures only matching records are updated.

MS SQL Server:

UPDATE Sales_Import
SET AccountNumber = RAN.AccountNumber
FROM Sales_Import SI
INNER JOIN RetrieveAccountNumber RAN ON SI.LeadID = RAN.LeadID;

MySQL and MariaDB:

UPDATE Sales_Import SI, RetrieveAccountNumber RAN
SET SI.AccountNumber = RAN.AccountNumber
WHERE SI.LeadID = RAN.LeadID;

Explanation:

• UPDATE Sales_Import: This specifies the target table for the update operation.
• SET AccountNumber = RAN.AccountNumber: This assigns the AccountNumber from the RetrieveAccountNumber table (RAN.AccountNumber) to the AccountNumber column in the Sales_Import table.
• FROM Sales_Import SI INNER JOIN RetrieveAccountNumber RAN ON SI.LeadID = RAN.LeadID (MS SQL) or UPDATE Sales_Import SI, RetrieveAccountNumber RAN WHERE SI.LeadID = RAN.LeadID (MySQL/MariaDB): This joins the two tables based on the LeadID, ensuring that only rows with matching IDs are updated. The INNER JOIN (MS SQL) or WHERE clause (MySQL/MariaDB) acts as the matching criteria.

This approach directly addresses the issue of null values by only updating rows where a matching LeadID exists in both tables. This results in a cleaner and more efficient update process.

The above is the detailed content of How to Update One SQL Table from Another Using a Matching ID?. For more information, please follow other related articles on the PHP Chinese website!