Update Primary/Foreign Key Columns of Numeric/Character Datatype in Oracle

Often the existing data in the database needs to get updated, even if they are part of any primary or foreign keys. In Oracle8.0, it is possible to defer integrity constraints but this feature is unavailable in earlier versions.

This script takes the column name, the new value for the column, and an optional WHERE clause. The WHERE clause should not cause any data conflict when the integrity constraints need to be enabled.

 set termout off;set echo off;set heading off;set pause off;set pagesize 1000;set linesize 120;set feedback off;set trunc off;set termout on;set serveroutput on size 1000000set feed off veri off trims off lines 80Accept v_column prompt "Enter column name to be updated : " promptAccept v_newvalue prompt "Enter the new valid column value without quotation marks"PromptPrompt Now enter a valid where clause, if any, starting with 'WHERE', Prompt else just hit Carriage Return.Prompt If an invalid or conflicting where clause is provided, Prompt constraints may not get enabled.Prompt If the WHERE clause includes any character literal, Prompt then prefix and suffix each of them with two singleAccept v_whereclause prompt "quotation marks : "promptspool TempUpdateColumn.sqlSELECT 'alter table '|| A.table_name || ' disable constraint '||A.constraint_name || ';'FROM user_cons_columns A, user_constraints BWHERE A.constraint_name = B.constraint_nameAND A.table_name = B.table_nameAND A.column_name = Upper('&v_column')AND B.constraint_type = 'R' ;SELECT 'alter table '|| A.table_name || ' disable constraint '||A.constraint_name || ';'FROM user_cons_columns A, user_constraints BWHERE A.constraint_name = B.constraint_nameAND A.table_name = B.table_nameAND A.column_name = Upper('&v_column')AND B.constraint_type in ('P','U');SELECT 'update '|| table_name || ' set '|| '&v_column' || '=''' ||'&v_newvalue' ||''''||' '|| '&v_whereclause' || ';'FROM user_col_commentsWHERE column_name = Upper('&v_column');SELECT 'alter table '|| A.table_name || ' enable constraint '||A.constraint_name || ';'FROM user_cons_columns A, user_constraints BWHERE A.constraint_name = B.constraint_nameAND A.table_name = B.table_nameAND A.column_name = Upper('&v_column')AND B.constraint_type in ('P','U');SELECT 'alter table '|| A.table_name || ' enable constraint '||A.constraint_name || ';'FROM user_cons_columns A, user_constraints BWHERE A.constraint_name = B.constraint_nameAND A.table_name = B.table_nameAND A.column_name = Upper('&v_column')AND B.constraint_type = 'R' ;SELECT 'SPOOL OFF' FROM DUAL;set termout onset pagesize 58set linesize 80set feedback on/* Run query generated above */spool TempUpdateColumn.logstart TempUpdateColumn.sqlset verify onset echo onset heading onspool off;
Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

homes in the real estate industry

Exploring the Latest Tech Trends Impacting the Real Estate Industry

The real estate industry is changing thanks to the newest technological advancements. These new developments — from blockchain and AI to virtual reality and 3D printing — are poised to change how we buy and sell homes. Real estate brokers, buyers, sellers, wholesale real estate professionals, fix and flippers, and beyond may

man on floor with data

DevX Quick Guide to Data Ingestion

One of the biggest trends of the 21st century is the massive surge in internet usage. With major innovations such as smart technology, social media, and online shopping sites, the internet has become an essential part of everyday life for a large portion of the population. Due to this internet

payment via phone

7 Ways Technology Has Changed Traditional Payments

In today’s digital world, technology has changed how we make payments. From contactless cards to mobile wallets, it’s now easier to pay for goods and services without carrying cash or using a checkbook. This article will look at seven of the most significant ways technology has transformed traditional payment methods.