Untitled

-- Шаг 1: Определение уникальных пар client_inn и agent_inn
;WITH UniqueAgentPairs AS (
    SELECT 
        client_inn, 
        agent_inn
    FROM #vttr
    GROUP BY client_inn, agent_inn
),
-- Шаг 2: Подсчет количества уникальных agent_inn для каждого client_inn
UniqueAgentCount AS (
    SELECT 
        client_inn,
        COUNT(agent_inn) AS UniqueAgentCount
    FROM UniqueAgentPairs
    GROUP BY client_inn
    HAVING COUNT(agent_inn) = 1 -- Шаг 3: Выбор client_inn с одним уникальным agent_inn
),
-- Шаг 4: Подготовка к обновлению, выборка нужных client_inn и соответствующих им agent_inn
AgentsToUpdate AS (
    SELECT 
        uac.client_inn,
        uap.agent_inn
    FROM UniqueAgentCount uac
    JOIN UniqueAgentPairs uap ON uac.client_inn = uap.client_inn
)
-- Обновление #KP_CommonClientsOkved
UPDATE kpc
SET kpc.agent_inn = atu.agent_inn
FROM #KP_CommonClientsOkved kpc
JOIN AgentsToUpdate atu ON kpc.client_inn = atu.client_inn
WHERE kpc.agent_inn IS NULL;