CODEWITHSUNDEEP
sqlpostgresqlduplicatessql-update
Nadhas
Nadhas

Reputation: 5807

How to find duplicate records and update using postgresql?

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

Upvotes: 0

Views: 509

Answers (3)

Nadhas
Nadhas

Reputation: 5807

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;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269933

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;

Upvotes: 0

Rahul Sawant
Rahul Sawant

Reputation: 1264

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

Upvotes: 2

Related Questions