Пример обновления данных в одной таблице с одновременным сопоставлением и проверкой условия по второй таблице при помощи JOIN:

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

ClientsCards cc - Первая таблица в которой производится изменение.

ClientsCardsHeads cch - Вторая таблица с данными которой производится сверка.