-- https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/UPDATE.html
CREATE TABLE people_demo1 OF people_typ;
CREATE TABLE people_demo2 OF people_typ;
UPDATE people_demo1 p SET VALUE(p) =
   (SELECT VALUE(q) FROM people_demo2 q
    WHERE p.department_id = q.department_id)
   WHERE p.department_id = 10;