CODEWITHSUNDEEP
sqlpostgresqlsql-updategreatest-n-per-group
MCM13
MCM13

Reputation: 275

How To Update MAX() Value - PostgreSQL

I have 2 tables:

tableA

id  name    group_id    group_name  sub_state
1   User1       1            MG      active
2   User3       3            AG      active

TableB

tableA_id       v_date                group_id
   1        2020-01-20T21:51:24.000Z     1          
   2        2020-03-10T21:20:24.000Z     3   
   1        2020-05-05T23:20:24.000Z     1   
   2        2020-05-13T23:20:24.000Z     3

I want to update the most recent v_date of each user for a new date. I believe using MAX() would be useful here.

I have tried:

UPDATE tableB
SET v_date = '2020-05-27 20:00:13+00'
WHERE v_date = (SELECT MAX(v_date) FROM tableB
     LEFT JOIN tableA ON tableB.tableA_id = tableA.id AND tableB.group_id = tableA.group_id
     WHERE tableA.sub_state = 'active'
     GROUP BY tableA.id);

This query returns the error: Query Error: error: more than one row returned by a subquery used as an expression

Then I tried:

WITH stat AS (
    SELECT MAX(v_date) FROM tableB
    LEFT JOIN tableA ON tableB.tableA_id = tableA.id AND tableB.group_id = tableA.group_id
    WHERE tableA.sub_state = 'active' AND tableA.group_id = 1
    GROUP BY tableA.id
)   
UPDATE tableB
SET v_date = '2020-05-27 20:00:13+00'
FROM stat
WHERE group_id = 1;

This changes the date of all the records, not just the last one or recent one.

How can I update the last record from each user without affecting all the records from all the time?

Upvotes: 0

Views: 1119

Answers (1)

GMB
GMB

Reputation: 222432

I think that you want:

UPDATE tableB
SET v_date = '2020-05-27 20:00:13+00'
WHERE v_date = (
    SELECT MAX(tb1.v_date) 
    FROM tableB tb1
    WHERE tb1.tableA_id = tableB.tableA_id
)

Upvotes: 1

Related Questions