This should get you started...

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:

SELECTt1.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