SQL - Updating table rows based on anothers table content [duplicate]

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

Gordon Linoff · Accepted Answer · 2018-11-13 21:50:04Z

0

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;

answered Nov 13, 2018 at 21:50

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL - Updating table rows based on anothers table content [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related