Unanswered: Updating all records with a single query
I am using forms 6i. One of my forms is calculating experience of employees by subtracting (sysdate-dateofjoining)/365 in post-text trigger which is working fine.
The database is having approx. 1000 records. Now what I want is on a single click of a button all the records will be updated i.e. on a single click of a button all the employee records will be having the latest (calculated from the current system date) experience of all the employees.
Kindly suggest me how this is going to be possible without going to each and every record.
The button should call a PL/SQL process that does this:
set experience = (sysdate-dateofjoining)/365;
However, it seems rather pointless storing "experience" as a column at all, since it becomes out of date tomorrow and needs recalculating periodically. I would instead derive the column in a view based on employees, or in 11G perhaps create a virtual column.