How to update table in pl/sql?

Example contrived for this question. Two tables, Project and Employee. Employee has a project id foreign key. Query needs to

1. select all the projects that are of year 2021 with ongoing status in Employee table

2. for each record from above select statement (1),

   • create a new row in project table and set year to 2022.

   • Get project Id (step 3) of this newly created row in project table, and update all the foreign keys in employee table (replacing old with new project id for each corresponding record)

Project Table

id | name            | year | 
1  | alpha           | 2021 | 
2  | groundwork      | 2020 | 
3  | NETOS           | 2021 | 
5  | WebOPD          | 2019 | 

Employee table

id | name  | year  | status     | project name     | projectID 
1  | john  | 2021  | ongoing    | alpha            | 1 
2  | linda | 2021  | completed  | NETOS            | 3 
3  | pat   | 2021  | WebOPD     | completed        | 5
4  | tom   | 2021  | ongoing    | alpha            | 1

insert into project with projects as 
(select max(id) over() max_p_id, id, name, year from project), temp as ( select distinct projectid, project_name from employee where status='ongoing') select max_p_id+row_number() over(order by p.id), p.name, 2022 from projects p join temp e on p.id=e.projectid where p.year=2021; 

Answer

declare
id_num project.project_id%type;
begin

/*for each loop u asked*/

for i in (select distinct p.name,p.project_id
from project p, employee e where p.name=e.project_name and 
p.year=to_date('2021','YYYY')
and e.status = 'ongoing') loop

/*here i made new unique id for project and saved it into id_num variable, you 
can use a sequence here it would be a better solution but I did it with a 
for loop*/

  for i in (select max(project_id) id_num from project)loop
    id_num:=i.id_num+1;
  end loop;

  /*inserting new project with same name, new date and new id*/

  insert into project values (id_num,i.name,to_date('2022','YYYY'));

  /*updating the employee table where is the old project_id*/

  update employee set employee.project_id=id_num where 
  employee.project_id=i.project_id; 
end loop;
end;

I have commented the code make sure u read it, hope this helps.