Sno	SID	Sname
1	Null	A
2	Null	B
3	Null	C
4	Null	D
5	Null	E

ano	aID	aName
1	55	AA
2	32	BB
3	53	CC
4	10	DD
5	10	EE

 
Sno	SID	Sname
1	55	A
2	32	B
3	53	C
4	10	D
5	10	E

IF OBJECT_ID('tempdb..#table1') IS NOT NULL
	DROP TABLE #table1;

IF OBJECT_ID('tempdb..#table2') IS NOT NULL
	DROP TABLE #table2;

CREATE TABLE #table1 (sno int unique NOT NULL, [sid] int NULL, sname varchar(2) unique NOT NULL);
CREATE TABLE #table2 (ano int unique NOT NULL, [aid] int NULL, aname varchar(2) unique NOT NULL);

INSERT INTO #table1
	SELECT 1,Null,'A' UNION ALL
	SELECT 2,Null,'B' UNION ALL
	SELECT 3,Null,'C' UNION ALL
	SELECT 4,Null,'D' UNION ALL
	SELECT 5,Null,'E';

INSERT INTO #table2
	SELECT 1,55,'AA' UNION ALL
	SELECT 2,32,'BB' UNION ALL
	SELECT 3,53,'CC' UNION ALL
	SELECT 4,10,'DD' UNION ALL
	SELECT 5,10,'EE';

-- Here's what we need #table 1 to look like:
SELECT	t1.sno, 
		t2.aid,
		t1.sname
FROM #table1 t1
JOIN #table2 t2 ON t1.sno=t2.ano;

-- This will do it (Update based on a join)
UPDATE t1
SET t1.[sid]=t2.aid	
FROM #table1 t1
JOIN #table2 t2 ON t1.sno=t2.ano;

-- did it work? 
SELECT * FROM #table1;

--cleanup
DROP TABLE #table1;
DROP TABLE #table2;
GO

update #Table1
set #Table1.SID = #Table2.aID
from #Table2
where #Table1.Sno = #Table2.Sno ;

update #Table1
set #Table1.SID = #Table2.aID
from #Table2
where #Table1.Sno = #Table2.Sno ;

update #Table1
set SID = (SELECT #Table2.aID from #Table2 where #Table1.Sno = #Table2.Sno) ;