title: How to update a column value in one database table using another database table as a reference with SQL
body: <p>I have one SQL server database which was created using Import/Export Wizard from an Access Database. I then have another SQL database which is based upon the Access database but has a slightly different schema. I am trying to get the data from the simple Access based database into the more complex SQL server schema database. The Access database simply stored the Country as text in Contact table and in my new schema the Contact table has a CountryId which links to the Country table. I've tried to write the SQL to do this:</p>
  
  <pre><code>UDPATE SQLVersion.dbo.Contact
  SET CountryId = (SELECT LookupCountry.Id
                   FROM SQLVersion.dbo.Country as LookupCountry, AccessDBVersion.dbo.tblContact as AccessContact
                   WHERE LookupCountry.Name = AccessContact.Country);
  </code></pre>
  
  <p>This doesn't work because:</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.</p>
  </blockquote>
  
  <p>I can understand why this error is thrown but I don't know how to form the correct SQL that will allow it to update each row with the value found from the lookup. Can anyone help please?</p>