I want to rename a value in a table whose main role is as an enumeration. That is, it’s a value used in foreign keys from quite a few other tables.
This example is Oracle-specific. I want to insert my new row as a copy of an existing row.
insert into some_table (some_table_id, column_1, column_2) select 'NEW VALUE', column_1, column_2 from some_table where some_table_id = 'OLD VALUE';
Then I want to figure out all of the foreign key constraints:
select * from sys.all_cons_columns where column_name='SOME_TABLE_ID' and owner='SOME_OWNER';
For each of the dependent tables, I want to update their rows to point to the new value.
update DEPENDENT_TABLE set SOME_TABLE_ID to 'NEW VALUE' where SOME_TABLE_ID = 'OLD VALUE';
Finally, I want to remove the old value from the original table:
delete from SOME_TABLE where SOME_TABLE_ID = 'OLD VALUE';