Today's Messages (off)  | Unanswered Messages (on)

Forum: SQL & PL/SQL
 Topic: update foreign key of all the referenced table of table
update foreign key of all the referenced table of table [message #681637] Mon, 10 August 2020 23:26
sinha73
Messages: 5
Registered: November 2006
Location: ST LOUISELOPER
Junior Member

I am working on the conversion of tables from the old system to the new system.
I want to update the foreign key value of the referenced table of my table with the new value.
I ran the following query to get the result of all referenced table:

SELECT *
FROM all_constraints
WHERE r_constraint_name IN (SELECT constraint_name
FROM all_constraints
WHERE table_name = 'ADDR');

Query results in 20 rows of the referenced table. I want to loop this table and make the updates.

I want to update the FK value in all the referenced tables with the new value that I will get from a temptable(CONV_TB_2315_1) that has old_id(id) from the ADDR table and new_id(ADDR_id) from the new_ADDR table.

UPDATE
ref_tab_1 rt
SET
ADDR_ID = (
SELECT
ADDR_ID
FROM
CONV_TB_2315_1 CONV
WHERE
rt.ADDR_ID = CONV.id
),
UPDATED_BY = STAFF_ID
where
rt,ADDR_ID IS NOT NULL;

how can I loop all the tables to make the updates?




Current Time: Fri Aug 14 05:07:56 CDT 2020