Difference between CTE and Temp Table and Table Variable

http://www.dotnet-tricks.com/Tutorial/sqlserver/X517150913-Difference-between-CTE-and-Temp-Table-and-Table-Variable.html

update multiple rows single query sql server

declare @SymptomID int=46

update MD_RS_SymptomsReviewQuestions
 set Sequence = bb.Number
 from MD_RS_SymptomsReviewQuestions
 join
 (

select a.* --ROW_NUMBER() over (ORDER BY id) AS Number
from MD_RS_SymptomsReviewQuestions a
where a.SymptomID = @SymptomID
and a.IsDeleted = 0
and a.Sequence <= 0
 )aa
 on aa.ID = MD_RS_SymptomsReviewQuestions.ID
 join
 (
select b.ID, ROW_NUMBER() over (ORDER BY id) AS Number
from MD_RS_SymptomsReviewQuestions b
where b.SymptomID = @SymptomID
and b.IsDeleted = 0
and b.Sequence <= 0
 )bb
 on bb.ID = aa.ID