sql - SQL根据ID匹配从一个表更新到另一个表(SQL update from one Table to another based on a ID match)

Question

I have a database with account numbers and card numbers .

(我有一个包含account numbers和card numbers的数据库。)

I match these to a file to update any card numbers to the account number, so that I am only working with account numbers.

(我将这些文件与文件匹配，以将所有卡号update为该帐号，因此我仅使用帐号。)

I created a view linking the table to the account/card database to return the Table ID and the related account number, and now I need to update those records where the ID matches with the Account Number.

(我创建了一个将表链接到帐户/卡数据库的视图，以返回Table ID和相关的帐号，现在我需要更新ID与帐号匹配的那些记录。)

This is the Sales_Import table, where the account number field needs to be updated:

(这是Sales_Import表，其中的account number字段需要更新：)

LeadID  AccountNumber
147         5807811235
150         5807811326
185         7006100100007267039

And this is the RetrieveAccountNumber table, where I need to update from:

(这是RetrieveAccountNumber表，我需要从其中更新：)

LeadID  AccountNumber
147         7006100100007266957
150         7006100100007267039

I tried the below, but no luck so far:

(我尝试了以下方法，但到目前为止没有运气：)

UPDATE [Sales_Lead].[dbo].[Sales_Import] 
SET    [AccountNumber] = (SELECT RetrieveAccountNumber.AccountNumber 
                          FROM   RetrieveAccountNumber 
                          WHERE  [Sales_Lead].[dbo].[Sales_Import]. LeadID = 
                                                RetrieveAccountNumber.LeadID) 

It updates the card numbers to account numbers, but the account numbers gets replaced by NULL

(它将卡号更新为帐号，但是帐号被NULL取代)

  ask by translate from so

Answer 1

I believe an UPDATE FROM with a JOIN will help:

(我相信带有JOIN的UPDATE FROM会有所帮助：)

MS SQL (MS SQL)

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

MySQL and MariaDB (MySQL和MariaDB)

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