SQL Update from One Table to Another Based on a ID Match

Question

Account and credit card numbers are stored in a database I hold. To ensure that I am just working with account numbers, I compare these to a file and update any card numbers to the account number.

I built a view connecting the table to the account/card database to return the Table ID and associated account number. Now I need to update the records where the ID and account number match.

The account number field in the Sales Import database needs to be modified.

LeadID	AccountNumber
147	5807811235
150	5807811326
185	7006100100007267039

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

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 get replaced by NULL

Answer 1

The solution is to UPDATE FROM with a JOIN to do this:

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

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

Answer 2

update sales_import s
set    s.AccountNumber = (
  select r.AccountNumber
  from  RetrieveAccountNumber r
  where  r.LeadID=s.LeadID
  and    s.AccountNumber=r.AccountNumber
)
where  exists (
  select null
  from   RetrieveAccountNumber r
  where  r.LeadID!=s.LeadID
  and    s.AccountNumber!=r.AccountNumber
);