Home » SQL & PL/SQL » SQL & PL/SQL » Help with incrementing with ROWNUM (Oracle 10g)
Help with incrementing with ROWNUM [message #433685] Thu, 03 December 2009 12:11 Go to next message
hammad83
Messages: 36
Registered: June 2008
Location: Pakistan
Member
Hey guys

I am stuck in a problem and need your help. I have a table in which I have two columns - NTN and Business_Serial. Some of the values in NTN are duplicate and same Business Serial is assigned to them. For example

NTN Business_Serial
---------------------------
123456 9000
123456 9000
123456 9000
123456 9000
787123 9000
787123 9000
787123 9000

Now I want that against every duplicate NTN, there should be a unique Business_Serial, and it has to be incremented by 1. So the table should be like this.

NTN Business_Serial
---------------------------
123456 9000
123456 9001
123456 9002
123456 9003
787123 9000
787123 9001
787123 9002

When I execute the query below.

update Business_Table a
set a.business_serial = a.business_serial+rownum 
where rowid > (select min(rowid) from Business_Table b
               where b.ntn = a.ntn)


I get this.

NTN Business_Serial
---------------------------
123456 9000
123456 9001
123456 9002
123456 9003
787123 9000
787123 9004
787123 9005


When I execute the query below.

update Business_Table a
set a.business_serial = a.business_serial+1 
where rowid > (select min(rowid) from Business_Table b
               where b.ntn = a.ntn)


I get this

NTN Business_Serial
---------------------------
123456 9000
123456 9001
123456 9001
123456 9001
787123 9000
787123 9001
787123 9001


Please help me with this, how can I increment the business_serial every time by 1 (following the previous business_serial for same NTN) when the new NTN starts.

Appreciate your help.

Thanks.

Hammad



icon10.gif  Re: Help with incrementing with ROWNUM [message #433693 is a reply to message #433685] Thu, 03 December 2009 12:28 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
Try this:
UPDATE   Business_table A
   SET   Business_serial =
             (SELECT   Business_serial + Rn -1
                FROM   (SELECT   Ntn, 
                                 ROW_NUMBER ()
                                     OVER (PARTITION BY Ntn ORDER BY ROWID)
                                     Rn
                          FROM   Business_table) B
               WHERE   A.ROWID = B.ROWID);

Shocked

[Updated on: Thu, 03 December 2009 12:31]

Report message to a moderator

Re: Help with incrementing with ROWNUM [message #433694 is a reply to message #433685] Thu, 03 December 2009 12:29 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Use the analytical function row_number() over ()
subtract 1 from the value and add that to the original column-value
Re: Help with incrementing with ROWNUM [message #433730 is a reply to message #433685] Thu, 03 December 2009 19:46 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
once you fix the data, find out the reason why the data got wrong in the first place.

then consider how you are going to maintain this nice neat number when someone does a delete or an insert.


Good luck, Kevin
Re: Help with incrementing with ROWNUM [message #433749 is a reply to message #433685] Fri, 04 December 2009 00:45 Go to previous messageGo to next message
hammad83
Messages: 36
Registered: June 2008
Location: Pakistan
Member
Thanks a lot for your help guys.

Actually I can't test it today - tomorrow I will test it on real data and let you know. Hope it will work. Smile

Hammad
Re: Help with incrementing with ROWNUM [message #434123 is a reply to message #433685] Mon, 07 December 2009 04:55 Go to previous message
hammad83
Messages: 36
Registered: June 2008
Location: Pakistan
Member
Hi everyone

Sorry for replying late - but this worked Smile

Thanks a lot guys.

Hammad
Previous Topic: converting to "\u" notation in Oracle
Next Topic: How to avoid the error - "ORA-00001: unique constraint violated"
Goto Forum:
  


Current Time: Mon Sep 26 17:55:28 CDT 2016

Total time taken to generate the page: 0.09308 seconds