-- Note, this example assumes that there is enough room in the dept_code column
-- to hold the newer, larger, values for the dept_codes.  If not, need to first
-- alter the data table to increase the size of the dept_code column.  Also
-- note to be careful of primary and foreign key constraints (test this in dev).

MYDBA@ORCL >
MYDBA@ORCL > start update_codes;
MYDBA@ORCL >
MYDBA@ORCL > create table data
  2  (
  3          dept_code varchar2(20),
  4          data varchar2(50)
  5  );

Table created.

MYDBA@ORCL >
MYDBA@ORCL > insert into data
  2  select mod(rownum,3)+1, rownum || 'blahblah' from all_objects where rownum <= 10;

10 rows created.

MYDBA@ORCL >
MYDBA@ORCL > commit;

Commit complete.

MYDBA@ORCL >
MYDBA@ORCL > select * from data;

DEPT_CODE            DATA
-------------------- --------------------------------------------------
2                    1blahblah
3                    2blahblah
1                    3blahblah
2                    4blahblah
3                    5blahblah
1                    6blahblah
2                    7blahblah
3                    8blahblah
1                    9blahblah
2                    10blahblah

10 rows selected.

MYDBA@ORCL >
MYDBA@ORCL > create table temp_dept_codes
  2  (
  3          old_dept_code varchar2(10),
  4          prefix varchar2(10),
  5          suffix varchar2(10)
  6  );

Table created.

MYDBA@ORCL >
MYDBA@ORCL > insert into temp_dept_codes values ('1', '100_', '111');

1 row created.

MYDBA@ORCL > insert into temp_dept_codes values ('2', '200_', '222');

1 row created.

MYDBA@ORCL > insert into temp_dept_codes values ('3', '300_', '333');

1 row created.

MYDBA@ORCL > commit;

Commit complete.

MYDBA@ORCL >
MYDBA@ORCL > select * from temp_dept_codes;

OLD_DEPT_C PREFIX     SUFFIX
---------- ---------- ----------
1          100_       111
2          200_       222
3          300_       333

3 rows selected.

MYDBA@ORCL >
MYDBA@ORCL > update data set dept_code =
  2  ( select prefix || suffix from temp_dept_codes where old_dept_code = dept_code);

10 rows updated.

MYDBA@ORCL >
MYDBA@ORCL > commit;

Commit complete.

MYDBA@ORCL >
MYDBA@ORCL > select * from data;

DEPT_CODE            DATA
-------------------- --------------------------------------------------
200_222              1blahblah
300_333              2blahblah
100_111              3blahblah
200_222              4blahblah
300_333              5blahblah
100_111              6blahblah
200_222              7blahblah
300_333              8blahblah
100_111              9blahblah
200_222              10blahblah

10 rows selected.

MYDBA@ORCL >
MYDBA@ORCL > drop table data;

Table dropped.

MYDBA@ORCL > drop table temp_dept_codes;

Table dropped.

MYDBA@ORCL >
MYDBA@ORCL > set echo off;
MYDBA@ORCL >