Update the Unique number for the co-related records between two columns in the group

I need to identify and update co-related records associated rank under Req_Result column as depicted below. Table name is tblSource.

+------+-----+-----------------+---------+
| Item | key | DenseRankWrtKey | Req_Res |
+------+-----+-----------------+---------+
| a    | 1   | 1               | 1       |
+------+-----+-----------------+---------+
| a    | 2   | 2               | 1       |
+------+-----+-----------------+---------+
| a    | 3   | 3               | 1       |
+------+-----+-----------------+---------+
| b    | 2   | 2               | 1       |
+------+-----+-----------------+---------+
| b    | 9   | 7               | 1       |
+------+-----+-----------------+---------+
| c    | 1   | 1               | 1       |
+------+-----+-----------------+---------+
| c    | 6   | 5               | 1       |
+------+-----+-----------------+---------+
| d    | 5   | 4               | 4       |
+------+-----+-----------------+---------+
| e    | 8   | 6               | 6       |
+------+-----+-----------------+---------+
| f    | 2   | 2               | 1       |
+------+-----+-----------------+---------+
| f    | 6   | 5               | 1       |
+------+-----+-----------------+---------+

Item and Key are co-related columns and DenseRankWrtKey is created by using Dense rank with respect to key. I need to assign the same DenseRankWrtKey values to all the co-related values.

Scenario explained:

Item a has the key value 1 and 1 is co-related with c as well, so all related values for a and 1 are a,b,c,f,2,3,7,6,5 hence all these values are assigned as 1 by referring DenseRank column, d and e are not further related to any other values hence its value is kept as is from DenseRank column.

I tried the queries

Update a 
SET a.Req_Res = b.DenseRankWrtKey 
from tblSource a 
inner join tblSource b on a.DenseRankWrtKey = b.DenseRankWrtKey

which is not sufficient.

Just try for this table too : DECLARE @Table AS TABLE ( Id INT IDENTITY(1,1) PRIMARY KEY ,Item varchar(100) ,[key] INT ,DenseRankWrtKey INT ,Req_Res INT ) INSERT INTO @Table ( Item ,[key] ,DenseRankWrtKey ) VALUES ('p', 10 ,1 ), ('q', 10 ,1 ), ('r', 20 ,2 ), ('s', 30 ,3 ), ('t', 30 ,3 ), ('u', 40 ,4 ), ('v', 40 ,4 ), ('w', 40 ,4 ), ('p', 50 ,5 ), ('q', 50 ,5 ), ('r', 50 ,5 ), ('s', 50 ,5 ), ('t', 50 ,5 ), ('u', 50 ,5 ), ('v', 50 ,5 ), ('w', 50 ,5 )

Answer

I find this way easier to read and maintain

DECLARE @TestTable TABLE (Item CHAR(1), ItemKey INT, DenseRankWrtKey INT, Req_Res INT)

INSERT @TestTable (Item, ItemKey, DenseRankWrtKey) VALUES 
('a'    , 1   , 1)
, ('a'    , 2   , 2)
, ('a'    , 3   , 3)
, ('b'    , 2   , 2)
, ('b'    , 9   , 7)
, ('c'    , 1   , 1)
, ('c'    , 6   , 5)
, ('d'    , 5   , 4)
, ('e'    , 8   , 6)
, ('f'    , 2   , 2)
, ('f'    , 6   , 5)

DECLARE @OtpTable TABLE (Item CHAR(1), ItemKey INT, DenseRankWrtKey INT)

DECLARE @RC INT = 1

WHILE @RC > 0
BEGIN

    DELETE @OtpTable

    ;WITH UpdateCTE AS (
      SELECT TOP 1  * from @TestTable 
      WHERE Req_Res IS NULL
    )
    UPDATE UpdateCTE 
    set Req_Res = DenseRankWrtKey
    OUTPUT Inserted.Item, Inserted.ItemKey, inserted.DenseRankWrtKey INTO @OtpTable

    SET @RC = @@ROWCOUNT

    WHILE @@ROWCOUNT > 0
    UPDATE T
    SET Req_Res = (SELECT TOP 1 DenseRankWrtKey FROM @OtpTable)
    OUTPUT Inserted.Item, Inserted.ItemKey, inserted.DenseRankWrtKey INTO @OtpTable
    FROM @TestTable T
    WHERE T.Req_Res IS NULL AND EXISTS (SELECT 1 FROM @OtpTable OT WHERE (T.Item = OT.Item OR T.ItemKey = OT.ItemKey))

END

SELECT * FROM @TestTable