4 Replies Latest reply: Aug 29, 2013 11:33 AM by InoL RSS

    Auto Updation

    910912

      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

       

       

      Thanks

      Liaqat

        • 1. Re: Auto Updation
          InoL

          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
            Soofi

            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..

             

            then

             

            begin

            for i in 1..1

            loop

            update table_name set record_no = rownum;

            end loop;

            end;

            • 3. Re: Auto Updation
              910912

              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

               

              Thanks

              Liaqta

              • 4. Re: Auto Updation
                InoL

                >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