title: Update columns from information of another table using variables with return of multiple rows
body: <p>I'm relatively new to SQL and have been searching for a solution for nearly a week now and wondering whether any expert here could give me some advice please:</p>
  
  <p>For start, I'm using SQL server management tool 2018 for my work. I have two tables X and Y, Table X contains column ID, A, B, C... and table Y contains column ID, Ax, Ay, Bx, By, Cx, Cy etc.</p>
  
  <p>I need update table X , column A=Ax+Ay if Ax>Ay, or A=Ay+Ax if Ax&lt;=Ay, so value in column A will be AxAy or AyAx.</p>
  
  <p>Same with Column B, C etc.</p>
  
  <p>I try to use declare function but it gives error message saying subquery returned more than 1 value, I understand that's probably because the set queries return multiple rows of data.But what's the best way of doing this?</p>
  
  <pre><code>declare @column varchar (20), @x varchar (20), @y varchar(20) 
  set @column=(select A from TableX);
  set @x=(select Ax from TableY) 
  set @y=(select Ay from TableY) 
  update TableX
  set @column=(@y+@x)
  from TableX 
  inner join TableY
  on TableX.ID=TableY.ID 
  where @x &lt;= @y
  </code></pre>
  
  <p>Please could you help?</p>
  
  <p>Many thanks in advance.</p>