title: Update Table using JOIN and Inside Query
body: <p>I have imported a Members table which has invalid States.  Instead of having the State Abbreviation, it contains the completed State Name.</p>
  
  <p>The below query does work and pulls up the Members.StateName and State.Abbreviation :</p>
  
  <pre><code>Connecticut CT
  Maryland    MD
  Massachusetts   MA
  </code></pre>
  
  <p>Now I am trying to write and Update command where I am replacing the Members.StateName with the State.Abbreviation .</p>
  
  <pre><code>UPDATE dbo.Members
  SET memState = (SELECT s.stAbv FROM dbo.State AS s
  JOIN Members AS m ON s.stName = m.memState )
  WHERE memState NOT IN (SELECT s.stAbv FROM dbo.State AS s)
  </code></pre>
  
  <p><em>ERROR: 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.</em></p>
  
  <p>The statement has been terminated.
  I understand the error says my inside query returns multiple rows, so it can not Update.  How do I get this to work? </p>