Home » SQL & PL/SQL » SQL & PL/SQL » Need help in SQL query using rownum
Need help in SQL query using rownum [message #318919] Thu, 08 May 2008 07:54 Go to next message
prashas_d
Messages: 66
Registered: February 2007
Member
Hi All,

I have a requirement in my project as follows:

Create table test(a varchar2(100), b number(10), c number(10), d varchar2(200));


If the input data is like

a b c
------- ---------- --------------
ABC 1 1
ABC 1 1
ABC 2 1
ABC 2 1
ABD 1 1
ABD 1 1
ABD 2 1
ABD 2 1
ABD 2 1
ABD 8 9
ABD 8 9

then after execution of query the field 'd' should be updated as

a b c d
------- ---------- ----------- -----------
ABC 1 1 ABC_1
ABC 1 1 ABC_1
ABC 2 1 ABC_2
ABC 2 1 ABC_2
ABD 1 1 ABD_1
ABD 1 1 ABD_1
ABD 2 1 ABD_2
ABD 2 1 ABD_2
ABD 2 1 ABD_2
ABD 8 9 ABD_3
ABD 8 9 ABD_3

I have tried using some query like

update TEST X set D = A || '_' || rownum 
where  (A, B, C) in (select A, B, C 
                     from TEST Y 
                     where Y.A = X.A
                     and Y.B = X.B 
                     and Y.C = X.C);


But it is populating values like ABC_2084, ABC_2085, etc instead of starting from 1 like ABC_1, ABC_2, etc

Can someone please help me in formulating the query?

Thanks,
prashas_d.
Re: Need help in SQL query using rownum [message #318921 is a reply to message #318919] Thu, 08 May 2008 07:57 Go to previous messageGo to next message
elaiyavel
Messages: 114
Registered: April 2008
Senior Member
update table1 set d = a||'_'||b
Re: Need help in SQL query using rownum [message #318925 is a reply to message #318921] Thu, 08 May 2008 08:02 Go to previous messageGo to next message
prashas_d
Messages: 66
Registered: February 2007
Member
My mistake.. I didnt explain clearly.

The value d should be populated as A || '_' || <sequence starting from 1>.

It should be populated for every unique combination of a, b and c. For example in the last two rows I gave 'B' value as 8 but the field 'D' is populates as ABD_3 but not ABD_8.
Re: Need help in SQL query using rownum [message #318926 is a reply to message #318919] Thu, 08 May 2008 08:03 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
You should read the Oracle Concepts manual on ROWNUM. It's not what you think it is.
Re: Need help in SQL query using rownum [message #318930 is a reply to message #318926] Thu, 08 May 2008 08:12 Go to previous messageGo to next message
prashas_d
Messages: 66
Registered: February 2007
Member
Can you please let me know if there any way to acheive it other than using rownum?
Re: Need help in SQL query using rownum [message #318931 is a reply to message #318930] Thu, 08 May 2008 08:15 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
You probably want to use the aggregate function ROW_NUMBER.
Re: Need help in SQL query using rownum [message #318938 is a reply to message #318930] Thu, 08 May 2008 08:33 Go to previous message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at ROW_NUMBER, RANK and DENSE_RANK functions.

Regards
Michel
Previous Topic: Why PLS-00306 error?
Next Topic: Right syntax
Goto Forum:
  


Current Time: Tue Dec 06 08:35:33 CST 2016

Total time taken to generate the page: 0.06706 seconds