Need to update column id using row_number()

I have a table with user_id ,user_name, rec columns. I have another column as ID. I want to populate the ID as below.

User_id	User_name	rec ID
123	ABC	rec 1
123	ABC	rec1 1
123	BCD	rec2 2
123	BCD	rec3 2
134	XYZ	rec1 1

I am not sure if this is possible, First it needs to find the unique user_id and its number of occurrences and then assign 1 to n number based on the occurrences but for a user_id if there are multiple records with same user_name then both rows should be updated with same id.

Previous this was achieved by row_number() by using user_id as partition where id would give 1 to n based on the user_id occurrence. ( Thanks to @MTO)

UPDATE table dst
SET id = (
           SELECT seq
           FROM   (
             SELECT row_number() over (partition by user_id order by user_id) as seq 
             FROM   test_csv
           ) src
           WHERE src.ROWID = dst.ROWID  
         );

Solution

Use DENSE_RANK rather than ROW_NUMBER:

UPDATE test_csv dst
SET id = ( SELECT seq
           FROM   (
             SELECT dense_rank() over (partition by user_id order by user_name)
                    as seq
             FROM   test_csv
           ) src
           WHERE src.ROWID = dst.ROWID
         );

or, using MERGE:

MERGE INTO test_csv dst
USING (
  SELECT dense_rank() over (partition by user_id order by user_name)
         as seq
  FROM   test_csv
) src
ON (src.ROWID = dst.ROWID)
WHEN MATCHED THEN
  UPDATE
  SET id = src.seq;

Which, for the sample data:

CREATE TABLE test_csv (user_id, user_name, rec, id) AS
SELECT 123, 'ABC', 'rec', CAST(NULL AS NUMBER) FROM DUAL UNION ALL
SELECT 123, 'ABC', 'rec1', NULL FROM DUAL UNION ALL
SELECT 123, 'BCD', 'rec2', NULL FROM DUAL UNION ALL
SELECT 123, 'BCD', 'rec3', NULL FROM DUAL UNION ALL
SELECT 134, 'XYZ', 'rec1', NULL FROM DUAL;

Then after the UPDATE or MERGE the table contains:

USER_ID	USER_NAME	REC	ID
123	ABC	rec	1
123	ABC	rec1	1
123	BCD	rec2	2
123	BCD	rec3	2
134	XYZ	rec1	1

fiddle