No consumable DDL or sample data supplied, so I couldn't test, but this should work after any necessary tweaking.

WITH KeysandCounts AS (
    SELECT
        TempKey
    ,    MAX(SLNNumber) AS SLNNumber
    ,    COUNT(*) AS NoofRows
    FROM StagingTable
    GROUP BY TempKey
    )
UPDATE d
SET Row_is_Active = 'No'
FROM DimensionTable d
JOIN KeysandCounts k
ON k.TempKey = d.Key
AND (k.NoofRows = 1 OR d.SLNNumber = 'Unknown')

John