Update rows PostgreSQL-CodePudding

everybody!

I have a table t(id, date1, date2):

id        date1           date2
 1    '2020-01-02'    '2020-01-02'
 1    '2020-01-12'    '2020-01-02'
 1    '2020-02-02'    '2020-01-02'
 1    '2020-03-02'    '2020-01-02'
 2    '2020-01-12'    '2020-01-02'
 2    '2020-01-15'    '2020-01-02'
 1    '2020-05-02'    '2020-01-02'
 1    '2020-06-02'    '2020-01-02'

I need to update it like this:

id        date1           date2
 1    '2020-01-02'    '2020-01-11'
 1    '2020-01-12'    '2020-02-01'
 1    '2020-02-02'    '2020-02-01'
 1    '2020-03-02'    '2020-05-01'
 2    '2020-01-12'    '2020-01-14'
 2    '2020-01-15'    '2999-12-31'
 1    '2020-05-02'    '2020-06-01'
 1    '2020-06-02'    '2999-12-31'

in rows with equal id:

date2 = date1 [from next row] - 1

and for the last date1 in group of equal id:

date2 = '2999-12-31'

CodePudding user response：

assuming you can order your columns by id and date1 in order to find which is the next row, you could use the LEAD, partitioned by id to get the next item in the list.

I replicate your case with:

create table test (id int, date1 date, date2 date);


insert into test values (1,'2020-01-02','2020-01-02');
insert into test values (1,'2020-01-12','2020-01-02');
insert into test values (1,'2020-02-02','2020-01-02');
insert into test values (1,'2020-03-02','2020-01-02');
insert into test values (2,'2020-01-12','2020-01-02');
insert into test values (2,'2020-01-15','2020-01-02');
insert into test values (1,'2020-05-02','2020-01-02');
insert into test values (1,'2020-06-02','2020-01-02');

And I could fetch the following date1 for each row with

select 
    id, 
    date1, 
    date2, 
    coalesce(lead(date1, 1) OVER (PARTITION BY id ORDER BY id, date1),'2999-12-31')  date2_next 
from test;

Result

 id |   date1    |   date2    | date2_next
---- ------------ ------------ ------------
  1 | 2020-01-02 | 2020-01-02 | 2020-01-12
  1 | 2020-01-12 | 2020-01-02 | 2020-02-02
  1 | 2020-02-02 | 2020-01-02 | 2020-03-02
  1 | 2020-03-02 | 2020-01-02 | 2020-05-02
  1 | 2020-05-02 | 2020-01-02 | 2020-06-02
  1 | 2020-06-02 | 2020-01-02 | 2999-12-31
  2 | 2020-01-12 | 2020-01-02 | 2020-01-15
  2 | 2020-01-15 | 2020-01-02 | 2999-12-31
(8 rows)

If you're looking for the update statement, check the one below

update test set date2=date2_next
from
(select id, 
    date1, 
    coalesce(lead(date1, 1) OVER (PARTITION BY id ORDER BY id, date1),'2999-12-31') date2_next 
from test) nxt
where test.id = nxt.id and test.date1=nxt.date1;