title: Subquery returned more than 1 value not allowed when the subquery is used as an expression
body: <p>My aim is to take data from one table and update it in another table. I am doing an explicit join and running the query below.</p>
  
  <pre><code>UPDATE [Users] set
    name = (
      SELECT concat(Column1,' ',Column2) 
      from [Table2] 
      inner join [Users] on Name = Column3
      where Name like 'ABC'
    )
  </code></pre>
  
  <p>I get the following error when I run the above query:</p>
  
  <blockquote>
    <p>Msg 512, Level 16, State 1, Line 45<br>
    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>Please note Column1, Column2, Column3 are unique to Table 2, hence I have not added a qualifier.</p>