Home » SQL & PL/SQL » SQL & PL/SQL » Updating Records in a table with max number (merged 2) 10g
Updating Records in a table with max number (merged 2) 10g [message #410174] Thu, 25 June 2009 09:39 Go to next message
Messages: 92
Registered: March 2007
Hi Folks

I have a table T1 with Sno column.
Below is the records which it holds

create table t1 (sno number);


I have a requirement to update null's with
max number and finally table should look like.


Please help me in this regard. I need update
statement to use in a procedure and i have to do
without using sequence.

Thanks in Advance
Re: Updating Records in a table with max number (merged 2) 10g [message #410187 is a reply to message #410174] Thu, 25 June 2009 10:27 Go to previous messageGo to next message
Michel Cadot
Messages: 63952
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
i have to do without using sequence.


I need update statement to use in a procedure

Take the max value and add the rownum for the rows with null value.

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 (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

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


[Edit: add missing "s"]

[Updated on: Fri, 26 June 2009 00:17]

Report message to a moderator

Re: Updating Records in a table with max number (merged 2) 10g [message #410262 is a reply to message #410187] Thu, 25 June 2009 22:39 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I will not ask WHY you cannot use a sequence number generator. It might be because there is some old school accounting thing going on, or that you have someone who is a SQLSERVER biggot and does not like using any Oracle advanced features. In any event, I do not want to discuss your solution design.

You should know that most people who do what you are asking do it wrong. They do not consider locking in a multi user environment and so create a solution that works in signle user mode but breaks in the real world.

To do what you ask, you must employ some kind of lock that ensures only one update at a time can happen to the table. Otherwise you will eventually have two processes counting the rows at the same time, each getting five and each figuring the next number is thus six and each updating a different null row so you end up with two rows labeled six.

So, here is a solution that does what you want and works.

   lock table t1 in exclusive mode;
   update t1 set sno =
        (select max(sno)+1 from t1)

if you are updating all rows at once, then you write variations of this theme that commit only once and are thus way more efficient. But you should be getting the point, you must lock the table so no one else can do anything with it for this to work.

I believe that in SQLSERVER, this is done be default. Which leads me to think that the person giving you advice might be trying to get you to write code they know without understanding that Oracle is a different animal and their old SQLSERVER practices won't work.

Good luck, Kevin
Previous Topic: Oracle "GLOBAL TEMPORARY" Tables
Next Topic: joining select statements
Goto Forum:

Current Time: Fri Oct 28 11:47:58 CDT 2016

Total time taken to generate the page: 0.11788 seconds