CODEWITHSUNDEEP
oracle10g
Raj
Raj

Reputation: 487

How to Update the column in whole Database?

Here is my situation, I have Table called EMPDETAILS(empcode, empname) with 10 records and some other tables that have empcode also.

**Example**
EMPCODE  EMPNAME
EMP001    A
EMP002    B
EMP003    C

Now I want to update the column EMPCODE EMP001 with 001 in whole Database

**OUTPUT**
EMPCODE EMPNAME
001       A

NOTE: 1. I Didn't set the primary key for the Parent Table.

Can any one please help?

Upvotes: 0

Views: 51

Answers (1)

Srini V
Srini V

Reputation: 11355

Try something like this to generate update statements

SELECT
       'UPDATE '
      || OWNER
      || '.'
      || TABLE_NAME
      || ' SET EMPCODE = SUBSTR ( EMPCODE, 1, 3); '
FROM
      SYS.ALL_TAB_COLUMNS
WHERE
      OWNER = '{owner}'
      AND COLUMN_NAME = 'EMPCODE';

Upvotes: 1

Related Questions