4 Replies Latest reply on Aug 29, 2013 4:33 PM by InoL

    Auto Updation


      Hi ,

      I have 5000 records in  table of  and  all record have  record no which is assign manually  to all records

      Employeed1                data     data     data                Recod No

      12345                              7     7          7                         1

      22222                              6     9          8                         2

      55555                              7     7          7                         3

      77777                              7     7          7                         4

      44444                              7     7          7                         5

      77878                              7     7          7                         6

      344334                            7     7          7                         7

      12121                              7     7          7                         8

      13131                              7     7          7                         9

      76897                              7     7          7                         10

      Probelm :-

      when i delete Record No 7 i need to  re-arrange other all number after  6 is there any idea to arrang them when i delete one record from oracle form





        • 1. Re: Auto Updation

          Is record_number a column in the table? That would be a very bad idea indeed. I cannot imagine any business requirement for this. Can you explain why you need this?

          If you just want it for display reasons, you can use rownum (for instance):


          select rownum, empno, ename

          from emp

          • 2. Re: Auto Updation

            Yes you can auto update the 'Record No' as InoL said it is a bad idea to have record_number a database column....


            And Moreover if you really want to regenerate the Record no..





            for i in 1..1


            update table_name set record_no = rownum;

            end loop;


            • 3. Re: Auto Updation

              Hi again


              actuall its employee record table where  pin , name , and other particulare  are saved  and i need to set seniority  number to each employee  suppose my table have 200  employee in table and i have set al employee seniorty number in seniority coloum when  at  employee at 67 position resigned i will delete that employee from table . . . i need to set all number again after 67 to 200. i want to  update automatically all number after 66 to 200  all employees seniority positon will changed after 66  . i hope you got my point


              rownum  is not solution  because managers have seprate seniority , engineers have diffrent and doctors have diffrent




              • 4. Re: Auto Updation

                >i have set al employee seniorty number in seniority coloum

                >managers have seprate seniority , engineers have diffrent and doctors have diffrent


                I still think it is a bad idea to have this column in your table. You are describing that you want to delete an employee, but what if

                - you want to insert an employee somewhere in the middle? Which number do you give it?

                - if an employee moves from engineer to manager? Does it get a new seniority, does it keep its seniority?


                What is the seniority based on? The date the employee was hired for instance? I think you should solve that with a RANK analytical function, like:


                select  empno, ename, job, hiredate

                      , rank() over  (partition by job order by hiredate) seniority

                from emp