title: How to update one columns data using another tables data TSQL
body: <p>I have just realised I have been capturing the wrong data for one column in my table. I have fixed the problem, however, the data I have captured thus far remains incorrect.</p>
  
  <p>Let's name my tables <code>TableIWantToCorrect</code> and <code>TableWithIDs</code></p>
  
  <p>In <code>TableIWantToCorrect</code>, I have a foreign key to <code>TableWithIDs</code>. This is what is incorrect.</p>
  
  <p>I am able to correct the data by comparing substring of a column in <code>TableIWantToCorrect</code> with a column in <code>TableWithIDs</code>.</p>
  
  <p>So currently, I have</p>
  
  <p><strong>TableIWantToCorrect</strong></p>
  
  <pre><code>Name            ForeignKey
  123-abc-123        15
  456-def-456        15
  789-ghi-789        15
  </code></pre>
  
  <p><strong>TableWithIDs</strong></p>
  
  <pre><code>CompareName    id
  abc            1
  def            2
  ghi            3
  </code></pre>
  
  <p>So I want to update <code>TableIWantToCorrect</code> to have the correct ForeignKey value when the substring in the Name equals the substring in Compare name. The position of the substring is always the same so I can use the <code>Substring</code> method.</p>
  
  <p>My attempt :</p>
  
  <pre><code>Update TableIWantToCorrect
  SET ForeignKey =
         (SELECT id 
          FROM TableWithIDs 
          WHERE UPPER(CompareName) = UPPER((SUBSTRING(TableIWantToCorrect.Name, 4, 3)))
  </code></pre>
  
  <p>The result :</p>
  
  <blockquote>
    <p>Subquery returned more than 1 value. This is not permitted when the
    subquery follows =, !=, &lt;, &lt;= , >, >= or when the subquery is used as
    an expression. The statement has been terminated.</p>
  </blockquote>
  
  <p>I know I have done something silly. What have I done incorrectly here ?</p>