-1

Say for example I have three records:

Name          Email          Group ID      Last update date     Master Record
dinesh.s    dinesh.s@xyz.com    10              1 Apr 2019  
dinesh.s    dinesh.s@xyz.com    10              7 Feb 2019  
dinesh.s    dinesh.s@xyz.com    10              20 Nov 2019      

The three record are exact matches with email so we consider they all belong to one group (Group ID).

I need to update in master record columns AS 'Y' based on frequently updated record, in this example we need to update 'Y' as below:

Name          Email          Group ID      Last update date     Master Record
dinesh.s    dinesh.s@xyz.com    10              1 Apr 2019  
dinesh.s    dinesh.s@xyz.com    10              7 Feb 2019  
dinesh.s    dinesh.s@xyz.com    10              20 Nov 2019         Y

Can you please help me to write a query for this in Postgres?

  • GROUP BY and MAX() should do the trick. – mustaccio Nov 20 '19 at 15:25
0

You can use a sub-select

update the_table
  set master_record = 'Y'
where (name, email, last_update_date) = (select name, email, max(last_update_date)
                                         from the_table
                                         group by name, email);

Another option would be to use a derived table, which might be faster:

update the_table
  set master_record = 'Y'
from (
  select name, email, max(last_update_date) as max_date
  from the_table
  group by name, email
) t
where t.name = the_table.name
  and t.email = the_table.email
  and t.max_date = the_table.last_update_date;

Both statements assume that the combination (name, email, last_update_date) is unique.

| improve this answer | |
  • Is this possible to get the same in the select statement itself? – danny Nov 21 '19 at 13:17
  • Sure, but why did you ask for an UPDATE then? – a_horse_with_no_name Nov 21 '19 at 13:39
  • Why might it be faster? – Vérace Nov 21 '19 at 13:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.