No es tan complicado. Usando tu consulta base
Código:
SELECT can_id,
fec_elab
contador,
ROW_NUMBER() OVER(PARTITION BY can_id order by fec_elab) as contador2
FROM SEG_CANCER_NAB
WHERE CAN_ID in (906930,905573)
/*
__Columnas en tabla___ Calculado
/ \ / \
+------+--------+--------+------------+
|can_id|fec_elab|contador|ROW_NUMBER()|
+------+--------+--------+------------+
|906930|20100101|NULL |1 |
+------+--------+--------+------------+
|906930|20100102|NULL |2 |
+------+--------+--------+------------+
|905573|20100101|NULL |1 |
+------+--------+--------+------------+
|905573|20100102|NULL |2 |
+------+--------+--------+------------+
|905573|20100103|NULL |3 |
+------+--------+--------+------------+
*/
Para cada fila tenemos un número de orden en cada grupo can_id. En teoría la operación de update sería tan simple como cambiar la consulta para actualizar la columna contador con el valor entregado por la función ROW_NUMBER:
Código:
UPDATE SEG_CANCER_NAB
SET contador = ROW_NUMBER() OVER(PARTITION BY ref.can_id ORDER BY ref.fec_elab)
FROM SEG_CANCER_NAB AS ref
WHERE ref.can_id in (906930,905573)
Pero la documentación indica que la función ROW_NUMBER sólo puede aparecer en las cláusulas SELECT y ORDER BY. Sin embargo ya tenemos todo lo que necesitamos, sólo se trata de actualizar una columna con el valor calculado. Así que es un caso ideal para usar una Common Table Expression (CTE). Para definir una, delimitamos la consulta con la cláusula WITH y le asignamos un nombre. La manera más fácil de comprenderla es si la imaginas como una vista definida temporalmente para la consulta. Al objeto definido se le pueden aplicar las mismas operaciones que a una vista (select, insert, delete, update). En resumen, la CTE mantendrá las referencias a los objetos originales con los cuales has definido la expresión (tablas, columnas, otras vistas). Por lo tanto podrás realizar todas las operaciones permitidas a los objetos base.
Saludos.