Oracle SQL Uniquely Update Duplicate Records

Question

I have a STUDENT table and need to update the STUDENT_ID values by prefixing with the letter SS followed by STUDENT_ID value. For any duplicate STUDENT_ID records, I should prefix the duplicate records as SS1 SS2. Below is an example

Before Update:

NUM	STUDENT_ID
1	9234
2	9234
3	9234
4	3456
5	3456
6	789
7	956

After Update:

NUM	STUDENT_ID
1	SS9234
2	SS19234
3	SS29234
4	SS3456
5	SS13456
6	SS789
7	SS956

Below is the query for updating the STUDENT_ID for unique records.

update student set student_id = 'SS'||student_id ;
commit;

Need suggestion for updating the STUDENT_ID for duplicate records. There are around 1 million duplicate records in the table and total volume is around 40 million. Appreciate for any inputs for performance enhancement.

Answer 1

You can use a MERGE statement correlated on the ROWID pseudo-column and using the ROW_NUMBER() analytic function:

MERGE INTO table_name dst
USING (
  SELECT ROWID as rid,
         ROW_NUMBER() OVER (PARTITION BY student_id ORDER BY num) AS rn
  FROM   table_name
) src
ON (src.rid = dst.ROWID)
WHEN MATCHED THEN
  UPDATE
  SET student_id = 'SS' || CASE WHEN rn > 1 THEN rn - 1 END || dst.student_id;

Which, for the sample data:

CREATE TABLE table_name (NUM, STUDENT_ID) AS
SELECT 1, CAST('9234' AS VARCHAR2(20)) FROM DUAL UNION ALL
SELECT 2, '9234' FROM DUAL UNION ALL
SELECT 3, '9234' FROM DUAL UNION ALL
SELECT 4, '3456' FROM DUAL UNION ALL
SELECT 5, '3456' FROM DUAL UNION ALL
SELECT 6, '789'  FROM DUAL UNION ALL
SELECT 7, '956'  FROM DUAL;

Then after the MERGE the table contains:

NUM	STUDENT_ID
1	SS9234
2	SS19234
3	SS29234
4	SS3456
5	SS13456
6	SS789
7	SS956

fiddle