Home » SQL & PL/SQL » SQL & PL/SQL » how to increment number in left pad if number already exists (oracle 10.1.3)
how to increment number in left pad if number already exists [message #393681] Tue, 24 March 2009 01:38 Go to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
i am updating every emp code by taking substring of first 2 letters in empcode and appending with 01.
my requirement is if 01 is already there then it should increment with next number.


UPDATE EMPLOYEES tl
   set  T1.EMP_CODE=
                 SUBSTR(TL.EMP_CODE, 0, 2) || '01';

empcode  result
******   ****** 
1IMPOA   1I01 
1IPOKK   1I02      
2KPOIAA  2K01
2KPOOO   2K02
2Kg2O    2K03
3QOPP    3Q01 

Re: how to increment number in left pad if number already exists [message #393683 is a reply to message #393681] Tue, 24 March 2009 01:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at ROW_NUMBER function.

Post a Test case: create table and insert statements along with the result you want with these data.

Regards
Michel

Re: how to increment number in left pad if number already exists [message #393699 is a reply to message #393681] Tue, 24 March 2009 01:57 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
	 CREATE TABLE EMPLOYEES
		 (
			 EMP_CODE VARCHAR2(15)
		 );

		 INSERT INTO EMPLOYEES VALUES('1IMPOA');-- EMP_CODE should be updated with 1IM01
		 INSERT INTO EMPLOYEES VALUES('1IPOKK');--EMP_CODE should be updated with 1IP01
		 INSERT INTO EMPLOYEES VALUES('2KPOIAA');--EMP_CODE should be updated with 2KP01
		 INSERT INTO EMPLOYEES VALUES('2KPOOO');--EMP_CODE should be updated with 2KP02
		 INSERT INTO EMPLOYEES VALUES('2Kg2O');--EMP_CODE should be updated with 2Kg01
		 INSERT INTO EMPLOYEES VALUES('3QOPP');--EMP_CODE should be updated with 3Q01
	         INSERT INTO EMPLOYEES VALUES('3QSPP');--EMP_CODE should be updated with 3Q02
	         INSERT INTO EMPLOYEES VALUES('3QSPP');--EMP_CODE should be updated with 3Q03
	         INSERT INTO EMPLOYEES VALUES('3QSPP');--EMP_CODE should be updated with 3Q04

How to change this update statement.
UPDATE EMPLOYEES tl
   set  T1.EMP_CODE= SUBSTR(TL.EMP_CODE, 0, 2) || '01' --this number should be increment;
Re: how to increment number in left pad if number already exists [message #393702 is a reply to message #393699] Tue, 24 March 2009 02:01 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the test case, now try to use the row_number function, first to select the correct value, then to update the field.

Note that your comments in the test case do not match with your example in the first post. So what are the requirements?

Regards
Michel

[Updated on: Tue, 24 March 2009 02:04]

Report message to a moderator

Previous Topic: Tuning the query
Next Topic: difference between 'H' and '''H'''
Goto Forum:
  


Current Time: Sun Dec 04 21:06:12 CST 2016

Total time taken to generate the page: 0.06403 seconds