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