| 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?
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
