How to make the joined data column as a new column in one table

Question

I have two tables A and B. Table A has the format like below:

id       task_start_time            task_end_time
__       _______________            _____________
1       2017-03-21 00:09:10     2017-03-21 00:12:18
1       2017-03-21 00:12:19     2017-03-21 00:12:56
1       2017-03-21 00:12:57     2017-03-21 00:13:10
2       2017-03-21 10:09:10     2017-03-21 10:25:34
2       2017-03-21 10:25:34     2017-03-21 11:09:10
2       2017-03-21 11:09:10     2017-03-21 11:21:39
3       2017-03-21 12:09:10     2017-03-21 12:19:19
3       2017-03-21 12:19:19     2017-03-21 12:29:19
3       2017-03-21 12:29:10     2017-03-21 12:39:10

Table B has the format like this

id          task_end_time                previous_task_end_time

__          _____________                ______________________
1          2017-03-21 00:12:18             NA
1          2017-03-21 00:12:56             2017-03-21 00:12:18 
1          2017-03-21 00:13:10             2017-03-21 00:12:56
2          2017-03-21 10:25:34             2017-03-21 10:25:34
2          2017-03-21 11:09:10             2017-03-21 11:09:10
2          2017-03-21 11:21:39             2017-03-21 11:21:39
3          2017-03-21 12:19:19             2017-03-21 12:19:19
3          2017-03-21 12:29:19             2017-03-21 12:29:19
3          2017-03-21 12:39:10             2017-03-21 12:39:10

Now I need to have the table A in this format

id       task_start_time            task_end_time        previous_task_end_time

__       _______________            _____________       ______________________
1       2017-03-21 00:09:10     2017-03-21 00:12:18             NA
1       2017-03-21 00:12:19     2017-03-21 00:12:56             2017-03-21 00:12:18 
1       2017-03-21 00:12:57     2017-03-21 00:13:10             2017-03-21 00:12:56
2       2017-03-21 10:09:10     2017-03-21 10:25:34             2017-03-21 10:25:34
2       2017-03-21 10:25:34     2017-03-21 11:09:10             2017-03-21 11:09:10
2       2017-03-21 11:09:10     2017-03-21 11:21:39             2017-03-21 11:21:39
3       2017-03-21 12:09:10     2017-03-21 12:19:19             2017-03-21 12:19:19
3       2017-03-21 12:19:19     2017-03-21 12:29:19             2017-03-21 12:29:19
3       2017-03-21 12:29:10     2017-03-21 12:39:10             2017-03-21 12:39:10

I know I can do a inner join them by id and start_time but how to save the resulting previous_end_time as a new column in A? Any help is appreciated

This might be helpful: https://stackoverflow.com/questions/18151372/sql-create-new-field-populate-with-query-results — alejandrogiron, May 29 '17 at 08:22

crvv · Accepted Answer · 2017-05-29T09:49:56.990

1

First, create the new column previous_end_time in table a.

ALTER TABLE a ADD COLUMN previous_end_time TIMESTAMP;

Then update the column.

UPDATE a SET previous_end_time = b.previous_task_end_time
FROM b WHERE a.id = b.id AND a.task_end_time = b.task_end_time

edited May 29 '17 at 09:49

answered May 29 '17 at 01:58

crvv

580
4
9

What do you mean by "did not work"? Without the detail, no one can resolve your problem. – crvv May 29 '17 at 07:12
I tried with `A as (select * from public.A), B as (select * from public.B) update A set previous_end_time from B where A.id = B.id and A.task_end_time=B.task_end_time` but it throws relation A does not exists – Ricky May 29 '17 at 07:31
Then there probably is no table `a` (but in your question you said that there is). – Laurenz Albe May 29 '17 at 07:44
there is public.A in my database – Ricky May 29 '17 at 08:02
Use `"A"` instead of `A` – crvv May 29 '17 at 09:49

How to make the joined data column as a new column in one table

1 Answers1