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

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;

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