This discussion is archived
4 Replies Latest reply: Aug 29, 2013 9:33 AM by InoL RSS

Auto Updation

910912 Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    >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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points