oracle cursor or loop statement for update

Question

I have employee table where it keep the history with Active data indicator , recently we have provided new Emp ID and the emp table got updated with active data indicator Y for all new employee ID update , where as old emp id got 'N'

Now i wanted to update the old emp id with new emp id , i have around 10K records with this scenrio , so is there any way that i can update all old emp id with new id ? is there any way i can update them ? i wanted to update only emp id column

data there:

Name	UNQ ID	EMP ID	Update date	Active Data
Sam D	q1212	12356	04/11/2022	Y
Sam D	q1212	00123	03/13/2020	N
Sam D	q1212	00123	12/12/2019	N

data i wanted to have:

Name	UNQ ID	EMP ID	Update date	Active Data
Sam D	q1212	12356	04/11/2022	Y
Sam D	q1212	12356	03/13/2020	N
Sam D	q1212	12356	12/12/2019	N

Since i am new to oracle , i have no idea how to do that

Answer 1

Assuming unq_id uniquely identifies your employee, a simple update should suffice by getting the emp_id for the same unq_id where the flag is Y:

UPDATE employee e1
   SET emp_id = (SELECT MAX(e2.emp_id)
                   FROM employee e2
                  WHERE e2.unq_id = e1.unq_id
                    AND e2.active_data = 'Y')
 WHERE active_data = 'N'