Update Query in SQL Server via JOINS

Question

I have 2 tables as mentioned below

create table #temp(id int, userid int,age int) 

insert into #temp values (1,1,1)
insert into #temp values(2,1,2)
insert into #temp values(3,1,3)

create table #tempMOCK(id int, userid int,age int) 
insert into #tempMOCK values (6,1,7)
insert into #tempMOCK values (7,1,9)

I want to update the first 2 rows of Mock table on #temp table. I am expecting that age of rowids 2 & 3 should become 7 & 9. I'm using this query but somehow it doesn't work.

UPDATE t1
SET    t1.age = t2.age
FROM   #temp t1
INNER JOIN #tempMOCK t2 ON t1.userid = t2.userid where t1.id in (1,2)

Answer 1

Since the user id is the same in all rows, you get the same value for the age.
This can be solved using a CTE, like this:

;with cte as 
(
  select id, userid, age, ROW_NUMBER() OVER(order by id) rn
  FROM #tempMock
)
UPDATE t1
SET    t1.age = t2.age
FROM   #temp t1
INNER JOIN cte t2 ON t1.userid = t2.userid and t1.id = t2.rn+1;

see fiddle here