UPDATE T SET SUBMIT=TT.COUNTTOTAL
FROM TEMPTABLE1 T INNER JOIN
(SELECT COUNT(*) FROM TEMPTABEL2 WHERE SUBMIT_YN='Y' GROUP BY POS_ID) TT
ON T.POS_ID=TT.POS_ID
UPDATE T SET SUBMIT=TT.COUNTTOTAL
FROM TEMPTABLE1 T INNER JOIN
(SELECT COUNT(*) FROM TEMPTABEL2 WHERE SUBMIT_YN='Y' GROUP BY POS_ID) TT
ON T.POS_ID=TT.POS_ID