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.