A simple way to do this:

update #Table1

set #Table1.SID = #Table2.aID

from #Table2

where #Table1.Sno = #Table2.Sno ;