How to find duplicate records and update using postgresql?

Question

id	userid	deviceid	isactive	last_modified (timestamp)
1	12	fdghfgh	true	2021-02-12
2	12	sdsdfg	true	2021-02-14
3	5	fghfgh	true	2021-01-12
4	15	dffdg	true	2021-02-14
5	15	dofghfjdog	true	2021-01-09

Only one device should be active for a user. Above table user 12 and 15 having two active devices.

How to set the recently modified device as active and other devices as false (for the corresponding user) in postgresql query?

Result should be:

id	userid	deviceid	isactive	last_modified (timestamp)
1	12	fdghfgh	false	2021-02-12
2	12	sdsdfg	true	2021-02-14
3	5	fghfgh	true	2021-01-12
4	15	dffdg	true	2021-02-14
5	15	dofghfjdog	false	2021-01-09

What query do you have so far? And why did you not just [edit your original question](https://stackoverflow.com/questions/66191293/how-to-find-duplicate-records-in-same-table-postgresql) — Charlieface, Feb 14 '21 at 22:40
Does this answer your question? [Postgresql : How to update one field for all duplicate values based at the end of the string of a field except one row](https://stackoverflow.com/questions/32403487/postgresql-how-to-update-one-field-for-all-duplicate-values-based-at-the-end-o) — astentx, Feb 14 '21 at 22:53

score 2 · Accepted Answer · answered Feb 14 '21 at 22:46

You can use RANK () OVER function like below

which will give you each entry ranked based on last modified date for each userid group.

Then you can write update query to update isactive to false where device_rank ! =1

select id,userid,deviceid,isactive,last_modified,
RANK () OVER ( 
        PARTITION BY userid
        ORDER BY last_modified DESC
    ) device_rank 
from deviceTable

score 1 · Answer 2 · answered Jul 06 '21 at 05:04

Query 1: (to rank the devices by last_modified_time)

select id,userid,deviceid,isactive,last_modified_timestamp,
RANK () OVER ( 
        PARTITION BY user_id
        ORDER BY last_modified_timestamp DESC
    ) device_rank 
from myschema.mytable rankTable

Query 2: (to update the device table to make only one active device - recent device)

UPDATE myschema.mytable ud
SET is_active = false
FROM (select id,userid,deviceid,isactive,last_modified_timestamp,
RANK () OVER ( 
        PARTITION BY user_id
        ORDER BY last_modified_timestamp DESC
    ) device_rank 
from myschema.mytable) as rankTable
WHERE ud.id=rankTable.id and rankTable.device_rank != 1;

score 0 · Answer 3 · answered Feb 15 '21 at 01:13

If you want to set the values, then use update. The subquery is used to calculate the date that should be considered active:

update t
    set is_active = (last_modified = max_lst_modified)
    from (select user_id, max(last_modified) as max_last_modified
          from t
          group by user_id
         ) tt
    where tt.user_id = t.user_id;

How to find duplicate records and update using postgresql?

3 Answers3