current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I have a table in which I have 4 columns: emp_no,desig_name,from_date and to_date:

emp_no  desig_name       from_date                  to_date
1001    engineer         2004-08-01 00:00:00
1001    sr.engineer      2010-08-01 00:00:00
1001    chief.engineer   2013-08-01 00:00:00

So my question is to update first row to_date column just one day before from_date of second row as well as for the second one aslo?

After update it should look like:

emp_no  desig_name       from_date                  to_date
1001    engineer         2004-08-01 00:00:00        2010-07-31 00:00:00
1001    sr.engineer      2010-08-01 00:00:00        2013-07-31 00:00:00
1001    chief.engineer   2013-08-01 00:00:00
share|improve this question
1  
Is there any unique field on this table? –  Jorge Campos Jan 20 at 11:25
    
yes there is also a colmn name 'promotion_id' that is unique field –  Anand Jan 20 at 11:34
    
Does promotion_id specify the order of the rows? –  vidaica Jan 20 at 11:42
    
yes it does ... –  Anand Jan 20 at 11:45
add comment

1 Answer

up vote 1 down vote

You can calculate the "next" date using the lead() function.

This calculated value can then be used to update the table:

with calc as (
  select promotion_id, 
         emp_no, 
         from_date, 
         lead(from_date) over (partition by emp_no order by from_date) as next_date
  from emp
)
update emp 
   set to_date = c.next_date - interval '1' day
from calc c
  where c.promotion_id = emp.promotion_id;

As you can see getting that value is quite easy, and storing derived information is very often not a good idea. You might want to consider a view that calculates this information on the fly so you don't need to update your table each time you insert a new row.

SQLFiddle example: http://sqlfiddle.com/#!15/31665/1

share|improve this answer
    
i m not getting it properly ,as per ur answer u first created the function as "calc" then u run the update query ? –  Anand Jan 20 at 12:14
    
and i m using PostgreSQL9.1 –  Anand Jan 20 at 12:14
    
@Anand: that is one single statement. There is no "function" in my statement. Did you have a look at the SQLFiddle? It will work with Postgres 9.1. And please refrain from using "leet speak". It is a sign of courtesy and respect to write proper sentences - especially if you are asking for free help –  a_horse_with_no_name Jan 20 at 12:16
    
i do apology for that . –  Anand Jan 20 at 12:25
    
when i m runing that script i m getting this error ERROR: syntax error at or near "update" LINE 8: update test_table ^ ********** Error ********** ERROR: syntax error at or near "update" SQL state: 42601 Character: 184 –  Anand Jan 20 at 12:29
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.