Home » SQL & PL/SQL » SQL & PL/SQL » cursor prblem?
icon4.gif  cursor prblem? [message #445523] Tue, 02 March 2010 05:57 Go to next message
jpmcbride
Messages: 2
Registered: March 2010
Location: Belfast
Junior Member
I am trying to update a table that looks like this example:

id claim seq
1 234
1 236
1 237
1 2311
2 2314
2 2317
3 2419
3 2420
3 2421

I need to fill out the seq column with a sequential number starting at 1 and reset back to 1 every time the product id changes so it ends up looking like this example:
id claim seq
1 234 1
1 236 2
1 237 3
1 2311 4
2 2314 1
2 2317 2
3 2419 1
3 2420 1
3 2421 1
I have thousands of rows so I assume some type of cursor or nested loop would be the answer.
Does anyone have a piece of pl/sql or sql that can do this?
Re: cursor prblem? [message #445527 is a reply to message #445523] Tue, 02 March 2010 06:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Have a look at ROW_NUMBER function.

If you post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: cursor prblem? [message #445529 is a reply to message #445523] Tue, 02 March 2010 06:10 Go to previous messageGo to next message
cookiemonster
Messages: 13955
Registered: September 2008
Location: Rainy Manchester
Senior Member
To be clear - you should be able to do this in a single update with the help of the row_number function Michel pointed you to. No cursors or PL/SQL should be needed.
icon14.gif  Re: cursor prblem? [message #445569 is a reply to message #445529] Tue, 02 March 2010 08:39 Go to previous message
jpmcbride
Messages: 2
Registered: March 2010
Location: Belfast
Junior Member
Well row_number certainly did the job!

Thanks for the pointer - Much apprciated.
Previous Topic: Getting Insert Statements
Next Topic: ORA-02049: timeout: distributed transaction waiting for lock
Goto Forum:
  


Current Time: Thu Nov 14 05:34:20 CST 2024