update value in first row of partition. sqlanywhere17

given a Table T

id	FKey	col_value
1	1	NULL
2	1	NULL
3	2	NULL
4	3	NULL
5	4	NULL
6	4	NULL
7	4	NULL
8	5	NULL
9	5	NULL

i want to Update each row with the highest id with value 1 for each given ForeignKey So my result looks like this:

id	FKey	col_value
1	1	1
2	1	NULL
3	2	1
4	3	1
5	4	1
6	4	NULL
7	4	NULL
8	5	1
9	5	NULL

please keep in mind that i am using sqlanywhere 17 so while

WITH cte AS(
SELECT id, fkey, col_value, 
ROW_NUMBER () OVER (PARTITION BY fkey ORDER BY ID ASC) AS rn
)
SELECT * FROM cte WHERE rn = 1

shows me the resultrows i need, i can not update them with

WITH cte AS(
SELECT id, fkey, col_value, 
ROW_NUMBER () OVER (PARTITION BY fkey ORDER BY ID ASC) AS rn
)
UPDATE cte SET value = 1

As is possible with other SQL systems and marked as a solution in countless other threads.

I just get syntaxerror at “UPDATE”

i am also unable join cte to table t and update that.

Answer

Core ANSI SQL solution, expected to execute on any dbms:

UPDATE T t1
SET Value = 1
WHERE id = (SELECT MIN(id) FROM T t2 WHERE t1.fkey = t2.fkey)

Note that VALUE is a SQL reserved word (https://en.wikipedia.org/wiki/SQL_reserved_words), might need to be delimited as "Value", or perhaps [Value].