I have a table point with the following (sample) data:
datekey pointkey filter_key t_key
-----------------------------------
(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)
The combination of the rows above can be same 1-6 times. With that I mean like the first 6 rows in the example above. There is no unique key in this table.This table has a couple of million rows like above.
I want to UPDATE this table to fill the NULL column (filter_key). In this column I want the values 1 and 2. When its a '2' in the t_key column the 2 must be also in the filter_key column. When its a 1 in the t_key column I only want the '1' once in the in the first record, all other columns must be '2'.
So the mentioned code above must be transfered into:
5506061 37 1 1
5506061 37 2 1
5506061 37 2 1
5506061 37 2 1
5506061 37 2 1
5506061 37 2 1
5506763 37 2 2
5506763 37 2 2
5506763 37 2 2
45643 97 2 2
45643 97 2 2
45643 97 2 2
45643 97 2 2
45643 97 2 2
45643 97 2 2
1234 83 1 1
1234 83 2 1
1234 83 2 1
1234 83 2 1
1234 83 2 1
1234 83 2 1
1234 84 1 1
1234 84 2 1
1234 84 2 1
1234 84 2 1
1234 84 2 1
1234 84 2 1
45645 97 1 1
45645 97 2 2
45645 97 2 2
45645 97 2 2
45645 97 2 2
45645 97 2 2
This can be done using Row_Number and CASE statement
'1' once in the in the first record to find the first record I have ordered the result with datekey,pointkey columns if you want to order the result using some other column then use that in Order by
SELECT datekey,
pointkey,
CASE WHEN t_key = 1 AND Row_number()OVER(Partition by datekey,pointkey ORDER BY (select null)) = 1 THEN 1 ELSE 2 END AS filter_key,
t_key
FROM Yourtable
If the result from above query is good enough then run the below update query
;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