Demo, Sql Server - rextester

Demo

CREATE TABLE Yourtable
    ([datekey] int, [pointkey] int, [filter_key] varchar(4), [t_key] int)
;
    
INSERT INTO Yourtable
    ([datekey], [pointkey], [filter_key], [t_key])
VALUES

(5506061, 37, NULL, 1),
(5506061, 37, NULL, 1),
(5506061, 37, NULL, 1),
(5506061, 37, NULL, 1),
(5506061, 37, NULL, 1),
(5506061, 37, NULL, 1),
(5506763, 37, NULL, 2),
(5506763, 37, NULL, 2),
(5506763, 37, NULL, 2),
(45643, 97, NULL, 2),
(45643, 97, NULL, 2),
(45643, 97, NULL, 2),
(45643, 97, NULL, 2),
(45643, 97, NULL, 2),
(45643, 97, NULL, 2),
(1234, 83, NULL, 1),
(1234, 83, NULL, 1),
(1234, 83, NULL, 1),
(1234, 83, NULL, 1),
(1234, 83, NULL, 1),
(1234, 83, NULL, 1),
(1234, 84, NULL, 1),
(1234, 84, NULL, 1),
(1234, 84, NULL, 1),
(1234, 84, NULL, 1),
(1234, 84, NULL, 1),
(1234, 84, NULL, 1),
(45645, 97, NULL, 1),
(45645, 97, NULL, 2),
(45645, 97, NULL, 2),
(45645, 97, NULL, 2),
(45645, 97, NULL, 2),
(45645, 97, NULL, 2)

;with cte as
	(
	SELECT datekey,
		   pointkey,
		   filter_key,
		   CASE WHEN t_key = 1 AND Row_number()OVER(Partition by datekey,[pointkey] ORDER BY (select null)) = 1 THEN 1 ELSE 2 END AS new_filter_key,
		   t_key
	FROM   Yourtable  
	)
    update cte set filter_key = new_filter_key

select * from Yourtable


run \| edit \| history \| help	0

Please log in to post a comment.