0

I have a column, person_id, in several tables in my database. I'm getting rid of duplicates in my individuals table and need to update the person_id in the rest of the tables.

My schema is clud and my tables are:

address
cases
child_cases
matter_person
phone
individuals

How can I update all the tables at once instead of doing them individually?

Individuals table

person_id | first_name | last_name
----------------------------------
100        John         Doe
101        John         Doe  (duplicate value)

I want to delete the record where person_id is 101 and I need to change the person_id in all other tables to 100 where the person_id is 101.

Cases Table

person_id | case_number
-----------------------
100        12345
101        6789

Result

person_id | case_number
-----------------------
100        12345
100        6789

matter_person Table

person_id | matter_number
-------------------------
100         123abc
101         456def

Result

person_id | matter_number
-------------------------
100        123abc
100        456def
belmer01
  • 119
  • 1
  • 7
  • 1
    Please provide sample data and desired results. – Gordon Linoff Nov 05 '20 at 19:35
  • 2
    In SQL's set-based paradigm and RDBMS' transaction-based [ACID](https://stackoverflow.com/questions/3740280/how-do-acid-and-database-transactions-work) framework, you would not be able to *update all the tables at once*. – Parfait Nov 05 '20 at 19:43
  • @GordonLinoff sample data is above – belmer01 Nov 05 '20 at 19:49
  • You would need a SQL transaction to update `person_id` in all the tables that reference the Individuals table, then delete the duplicate person records, and finally commit the transaction. – Hellmar Becker Nov 05 '20 at 20:57

1 Answers1

0

You can use Dynamic SQL such as

DECLARE
  v_pid_new individuals.person_id%type := 100;
  v_pid_old individuals.person_id%type := 101;  
BEGIN
  FOR c IN ( SELECT * FROM user_tab_cols WHERE column_name = 'PERSON_ID' )
  LOOP
   IF c.table_name = 'INDIVIDUALS' THEN
     EXECUTE IMMEDIATE 'DELETE '||c.table_name||' WHERE person_id=:pido' USING v_pid_old;     
   ELSE
     EXECUTE IMMEDIATE 'UPDATE '||c.table_name||' SET person_id=:pidn WHERE person_id=:pido' USING v_pid_new,v_pid_old;  
   END IF;  
     COMMIT;
  END LOOP; 
END;
/

Demo

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
  • If the person_id is used in foreign key constraints it may be necessary to order the statements to update child tables before parent tables. There may be other challenges (eg a person can only have one current/primary address) – Gary Myers Nov 05 '20 at 22:44
  • well, thanks @GaryMyers , that might also be considered. – Barbaros Özhan Nov 05 '20 at 22:57