DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: update on table

  1. #1
    Join Date
    Jul 2002
    Posts
    228

    update on table

    Hi,
    I've a table A with column:

    COD VARCHAR2(20)

    The col COD has this value:
    1234
    24788
    013456
    0356654 (there are so more of 1000 records)

    It's possible update (in automatic) this col with these new values?
    12000034
    24000788
    01003456
    03056654

    is necessary add zero until go to 8 charachters after first 2 char


    Thanks
    Raf

  2. #2
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    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..
    Code:
    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
    Last edited by Sameer; 01-24-2003 at 10:19 AM.

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    UPDATE my_table
    SET cod = SUBSTR(cod,1,2) || LPAD(SUBSTR(cod,3),6,'0');
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width