How to Update a Table's Account Number Using Data from Another Table Based on Matching IDs?

Database table data synchronization: update account number based on ID matching

In database operations, you often need to synchronize data in different tables based on public IDs. The goal of this example is to update the table named Sales_Import so that its AccountNumber fields match the corresponding RetrieveAccountNumber in the AccountNumber table.

The

solution is to use a JOIN statement that contains a UPDATE clause. By joining these two tables via a common LeadID field, we establish a relationship between records. This allows us to access the required data in the RetrieveAccountNumber table and update the Sales_Import fields in the AccountNumber table.

MS SQL syntax

<code class="language-sql">UPDATE
    Sales_Import
SET
    Sales_Import.AccountNumber = RAN.AccountNumber
FROM
    Sales_Import SI
INNER JOIN
    RetrieveAccountNumber RAN
ON 
    SI.LeadID = RAN.LeadID;</code>

MySQL and MariaDB syntax

<code class="language-sql">UPDATE
    Sales_Import SI,
    RetrieveAccountNumber RAN
SET
    SI.AccountNumber = RAN.AccountNumber
WHERE
    SI.LeadID = RAN.LeadID;</code>

After executing this query, the Sales_Import fields in the AccountNumber table will be updated with the corresponding RetrieveAccountNumber values ​​in the AccountNumber table. This data synchronization ensures that you use account numbers consistently across your database.

The above is the detailed content of How to Update a Table's Account Number Using Data from Another Table Based on Matching IDs?. For more information, please follow other related articles on the PHP Chinese website!