declare @tApplicant table (myID varchar(200), myCategory varchar(10) null);
insert into @tApplicant(myID) values('1925');
insert into @tApplicant(myID) values('4474');
insert into @tApplicant(myID) values('8398');
insert into @tApplicant(myID) values('4656');
insert into @tApplicant(myID) values('2288');
insert into @tApplicant(myID) values('4876');
/* myID is unique */



declare @tMySPM table (myID varchar(200), subjectCd varchar(10));
insert into @tMySPM values('1925','99');
insert into @tMySPM values('1925','19');
insert into @tMySPM values('2288','78');
insert into @tMySPM values('2288','99');
insert into @tMySPM values('4656','72');
/* Combination of myID and subjectCd is unique */


declare @tMySTPM table (myID varchar(200), subjectCd varchar(10));
insert into @tMySTPM values('4474','99');
insert into @tMySTPM values('4474','19');
insert into @tMySTPM values('2288','78');
insert into @tMySTPM values('2288','99');
insert into @tMySTPM values('8398','72');
/* Combination of myID and subjectCd is unique */

myID  | myCategory
-----------------------------
1925	A
4474	B
8398	B
4656	A
2288	A
4876	NULL

update @tApplicant 
set myCategory = 
(if exists(t2.myID) 
Begin
'A'
End
else if exists(t3.myID) 
Begin
'B'
End
else NULL 
end)

from @tApplicant t1
inner join @tMySPM t2 on t1.myID=t2.myID
inner join @tMySTPM t3 on t1.myID=t3.myID

UPDATE tA
SET tA.myCategory = 
	CASE 
		WHEN tM.myID IS NOT NULL THEN 'A'
		WHEN tMT.myID IS NOT NULL THEN 'B'
		ELSE NULL
	END
FROM @tApplicant tA
	LEFT JOIN @tMySPM tM
		ON tA.MyID = tM.MyID
	LEFT JOIN @tMySTPM tMT
		ON tA.MyID = tMT.MyID

update t
set myCategory = t1.Cat
from @tApplicant t
cross apply (select MAX('A') AS Cat
             from @tMySPM 
             where MyID = t.MyID
             UNION ALL
             select MAX('B')
             from @tMySTPM tMT
	     where MyID = t.MyID
            )t1

Update @tApplicant
Set myCategory = Case
		   When myCategory IS NULL AND Exists(Select myID From @tMySPM Where myID = @tApplicant.myID)
		   Then 'A'
		   When myCategory IS NULL AND Exists(Select myID From @tMySTPM Where myID = @tApplicant.myID)
		   Then 'B'
		   Else NULL
		 End