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 )
Advertisement
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