PostgreSQL query for multiple update

Question

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

yes there is also a colmn name 'promotion_id' that is unique field — Anand
– Anand, Commented Jan 20, 2014 at 11:34

user330315 · Accepted Answer · 2014-01-20 11:44:22Z

1

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

answered Jan 20, 2014 at 11:44

user330315

Sign up to request clarification or add additional context in comments.

7 Comments

Anand Over a year ago

i m not getting it properly ,as per ur answer u first created the function as "calc" then u run the update query ?

Anand Over a year ago

and i m using PostgreSQL9.1

user330315 Over a year ago

@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

Anand Over a year ago

i do apology for that .

Anand Over a year ago

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

|

Collectives™ on Stack Overflow

PostgreSQL query for multiple update

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related