-1

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)
vhu
  • 12,244
  • 11
  • 38
  • 48
Xander Kage
  • 161
  • 16

2 Answers2

0

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

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
0

you can try following query for this:

UPDATE #temp1 set age=(Select t2.age from #tempMOCK t2 
where t2.userid=#temp1.userid) where id in (1,2)

Hope it helps

Nikhil Gupta
  • 42
  • 7
  • this will raise an error: `Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.` – Zohar Peled Sep 07 '15 at 07:20