Para eso tienes ROW_NUMBER(). Usa el código que puse en la primera parte. Con eso solucionarás tu problema...
Es más, acabo de notar que puede ser aún más simple
Código:
WITH upd AS (
SELECT ROW_NUMBER() OVER(PARTITION BY can_id order by fec_elab) as contador2,
contador
FROM SEG_CANCER_NAB
WHERE CAN_ID in (906930,905573)
)
UPDATE upd
SET contador = contador2;
Saludos.