title: SQL Update from One Table to Another Based on a ID Match
body: <p>I have a database with <code>account numbers</code> and <code>card numbers</code>. I match these to a file to <code>update</code> any card numbers to the account number so that I am only working with account numbers.</p>
  <p>I created a view linking the table to the account/card database to return the <code>Table ID</code> and the related account number, and now I need to update those records where the ID matches the Account Number.</p>
  <p>This is the <code>Sales_Import</code> table, where the <code>account number</code> field needs to be updated:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>LeadID</th>
  <th>AccountNumber</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>147</td>
  <td>5807811235</td>
  </tr>
  <tr>
  <td>150</td>
  <td>5807811326</td>
  </tr>
  <tr>
  <td>185</td>
  <td>7006100100007267039</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>And this is the <code>RetrieveAccountNumber</code> table, where I need to update from:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>LeadID</th>
  <th>AccountNumber</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>147</td>
  <td>7006100100007266957</td>
  </tr>
  <tr>
  <td>150</td>
  <td>7006100100007267039</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I tried the below, but no luck so far:</p>
  <pre><code>UPDATE [Sales_Lead].[dbo].[Sales_Import] 
  SET    [AccountNumber] = (SELECT RetrieveAccountNumber.AccountNumber 
                            FROM   RetrieveAccountNumber 
                            WHERE  [Sales_Lead].[dbo].[Sales_Import]. LeadID = 
                                                  RetrieveAccountNumber.LeadID) 
  </code></pre>
  <p>It updates the card numbers to account numbers, but the account numbers get replaced by <code>NULL</code></p>