CODEWITHSUNDEEP
postgresqlgroup-bysql-updatepartition
Amiclone
Amiclone

Reputation: 408

Update column in postgresql using subquery

I have a table name "table1" like this in postgresql db:

CREATE TABLE table1(
id TEXT,
class INT,
r1 DATE,
r2 DATE
);



INSERT INTO public.table1 (id, class, r1, r2)
VALUES ('x2', 2, '1/25/2015', '1/25/2015');

INSERT INTO public.table1 (id, class, r1, r2)
VALUES ('x2', 3, '6/28/2015', '1/25/2015');

INSERT INTO public.table1 (id, class, r1, r2)
VALUES ('x2', 3, '6/18/2015', '1/25/2015');

INSERT INTO public.table1 (id, class, r1, r2)
VALUES ('x5', 2, '9/3/2015', '1/25/2015');

INSERT INTO public.table1 (id, class, r1, r2)
VALUES ('x3', 1, '9/3/2015', '1/25/2015');

INSERT INTO public.table1 (id, class, r1, r2)
VALUES ('x3', 1, '8/3/2015', '1/25/2015');

What I want to do is update column R2 such that for each set of id and class, R2 will be minimum of R1 for that group:

expected_output:

enter image description here

I am trying to do something like but it's not giving me correct result:


UPDATE table1
SET r2 = subquery.p FROM (select id, class, r1, min(r1) OVER (partition by class, id) AS p
FROM table1) AS subquery

Could someone please help me with the query?

Thanks

Upvotes: 0

Views: 28

Answers (1)

Nick
Nick

Reputation: 147196

This is probably most easily done using an update with join to a CTE of the minimum values:

WITH CTE AS (
  select id, class, min(r1) OVER (partition by class, id) AS p
  FROM table1
)
UPDATE table1 t1
SET r2 = CTE.p
FROM CTE
WHERE CTE.id = t1.id AND CTE.class = t1.class

Post update with your sample data:

id  class   r1          r2
x2  2       01/25/2015  01/25/2015
x2  3       06/28/2015  06/18/2015
x2  3       06/18/2015  06/18/2015
x3  1       09/03/2015  08/03/2015
x3  1       08/03/2015  08/03/2015
x5  2       09/03/2015  09/03/2015

Demo on dbfiddle

Upvotes: 1

Related Questions