update on table

[Sameer]

I guess for automatic update you need to write a trigger for this.. But hope this helps.. You can write code for trigger using this.. You can process the :NEW.column value with the SELECT statement as shown..

SQL> CREATE TABLE test (id VARCHAR2(10));

Table created.

SQL> INSERT INTO test VALUES ('1234');

1 row created.

SQL> INSERT INTO test VALUES ('24788');

1 row created.

SQL> INSERT INTO test VALUES ('013456');

1 row created.

SQL> INSERT INTO test VALUES ('0356654');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT CASE
  2            WHEN LENGTH (a.id) < 8 THEN (SELECT    SUBSTR (id, 1, 2)
  3                                              || LPAD ('0', 8 - LENGTH (id), '0')
  4                                              || SUBSTR (id, 3)
  5                                         FROM test
  6                                        WHERE id = a.id)
  7            ELSE id
  8         END Col
  9  FROM test a
 10  ;

COL
--------------------------------------------------------------------------------
12000034
24000788
01003456
03056654

SQL>

HTH

Sameer