-
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
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|