How to Update Account Numbers in One Table Based on Matching IDs in Another?

SQL inter-table update based on ID matching

To update account numbers based on card numbers with matching IDs, consider querying the data in multiple tables. Here's how to achieve this using SQL:

Get matching accounts

To associate account and card numbers, create a view or temporary table to link them using the public ID. For example, you can use the following view:

<code class="language-sql">CREATE VIEW AccountCardMatch AS
SELECT
  Sales_Import.LeadID,
  RetrieveAccountNumber.AccountNumber
FROM
  Sales_Import
INNER JOIN
  RetrieveAccountNumber
ON 
  Sales_Import.LeadID = RetrieveAccountNumber.LeadID;</code>

Update account number

To update the account number in Sales_Import with the corresponding matching value, use the UPDATE JOIN statement:

MS SQL

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

MySQL and MariaDB

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

This update operation will modify the account number in the Sales_Import table based on the matching LeadID.

The above is the detailed content of How to Update Account Numbers in One Table Based on Matching IDs in Another?. For more information, please follow other related articles on the PHP Chinese website!