SQL - Updating table rows based on anothers table content

Question

I have a temp table named #tempTab1 that contains such column as:

UserId, GroupId, Quantity

then I have another table #tempTab2 that has similar columns:

IdUser, IdGroup, ItemQuantity

I want to update the first's table Quantity column using the data from the second's table ItemQuantity column.

I've tried doing it using the following query:

UPDATE #tempTab1
SET #tempTab1.Quantity = #tempTab2.ItemQuantity
WHERE #tempTab1.UserId = #tempTab2.IdUser 
  AND #tempTab1.GroupId = #tempTab2.IdGroup

and what I get is

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Whats the best way to deal with it? Thank you in advance

Answer 1

You need a JOIN:

UPDATE t1
   SET Quantity = t2.ItemQuantity
FROM #tempTab1 t1 JOIN
     #tempTab2
     ON t1.UserId = t2.IdUser AND t1.GroupId = t2.IdGroup;