An exemple UPDATE WITH JOIN in MSSQL
update cc
set cc.ActivateState=2
from ClientsCards cc
left join ClientsCardsHeads cch
on cch.CardUid=cc.Uid
where cc.ActivateState=0 and cch.State=3
First table CC, that rows will changed.
Second table CCH, that used for compare data.