Unfortunately your logic won't work - where you have Itemstoupdate you need a constant value. You can achieve it using a Common Table Expression (CTE)
e.g.
;with CTE AS
(
SELECT ROW_NUMBER() OVER (ORDER BY NAME) AS RN,
name, type, purpose, flag
FROM #table2
)
Update A SET FLAG = 1
from CTE A
INNER JOIN #table1 B on A.type = B.type and A.purpose = B.purpose
WHERE A.RN <= B.itemstoupdate
The key point here is the
ORDER BY
clause on the
ROW_NUMBER
function -
TOP
doesn't have much context without
ORDER BY