updating second to last record in oracle sql

Question

I have 3 primary keys. CompanyName, EmpNo, StartDate.

I want to create a stored procedure that can allow me to update only the second to last record of each empno. How can I achieve that? In this case I have to records per empnno but I want to update only the second to last record regardless of the amount of record ex; If I have 100 records I want to update the 99th record of the same empno.

CompanyNo EmpNo    StartDate   FinalDate 
1           1     1999/01/01   2013/04/26   
1           1     2013/04/17   9999/12/31
1           2     1999/01/0    2013/04/26
1           2     2013/04/17   9999/12/31
1           3     1999/01/01   2013/04/26 
1           3     2013/04/17   9999/12/31

In this case I want to edit only the second to last in FinalDate. these ones;

CompanyNo EmpNo    StartDate    FinalDate   
1           1      1999/01/01   2013/04/26  <--- this finaldate field
1           2      1999/01/01   2013/04/26  <--- this finaldate field
1           3      1999/01/01   2013/04/26  <--- this finaldate field

What exactly do you mean with "edit the row"? Do you want to prevent updates on the other? What exactly do you want to update in that row? — , Apr 27 '13 at 07:57
I tried ordering my 3 primary keys by ASC and then tried using ROW_NUMBER but maybe I am missing something. — Edu, Apr 27 '13 at 08:07

Scotch · Answer 1 · 2013-04-27T08:14:40.447

0

Making assumptions about what you want, you can get the second finalfate for each empno / company by this

SELECT empNo FROM 
   (
   SELECT companyName, empNo, rank() over (PARTITION BY companyName,empNo
   ORDER BY FinalDate DESC) as rankings FROM yourTable
       ) WHERE ranking = 2

And you can do whatever update you need by

  Update yourTable set something = something WHERE empNo IN (  thatQueryAbove)

edited Apr 27 '13 at 08:14

answered Apr 27 '13 at 06:41

Scotch

3,186
11
35
50

I edited my question. Apologies. Maybe needed to be more explicit. Thank you in advance. – Edu Apr 27 '13 at 08:05
@a_horse_with_no_name for partitioning on multiple columns, is it necessary to enclose each column name in brackets? – Scotch Apr 27 '13 at 08:13
@a_horse_with_no_name you're right, I'm pretty sure it's parentheses though. Maybe brackets work as well. – Scotch Apr 27 '13 at 08:18

score 0 · Answer 2 · answered Apr 27 '13 at 08:14

0

I don't see why you need a stored procedure for this. What you describe can be done with a single statement:

merge into employment emp
using
(
   select CompanyName, 
          empno, 
          startdate, 
          finaldate, 
          row_number() over (partition by CompanyName, EmpNo order by StartDate desc) as rnk
   from employment
) t on (t.companyname = emp.companyname and t.empno = emp.empno and t.startdate = emp.startdate and t.rnk = 2)
when matched then update
     set finaldate = date '2013-04-28';

This will change the finaldate for those rows to '2013-04-28'

Although I have to admit I don't understand what you are really trying to do.

answered Apr 27 '13 at 08:14

I want to edit the second to last record of each employee. I will try to your code later today. – Edu Apr 28 '13 at 02:49
@Edu: the term "edit" does not make any sense with SQL. You update, delete or insert rows. You don't "edit" them. – Apr 28 '13 at 05:41
I mistook when commenting on your code but I used the term update in the title. Sorry for the confusion. – Edu Apr 30 '13 at 03:43

score -2 · Answer 3 · answered Apr 27 '13 at 06:31

-2

Oracle provides a pseudo-column called ROWNUM in queries. So if you know the total size of your dataset (count it), you can access to the second to last row using ROWNUM, get the primary key values and update it properly.

answered Apr 27 '13 at 06:31

Miguel Prz

13,718
29
42

updating second to last record in oracle sql

3 Answers3