update all duplicate rows with different values in pgsql

Question

I need to update duplicate rows with different values in same table. my table is

table(id, phoneId(int), deviceId(int), userId(int))

There are some records with same deviceId or phoneId. for example

id   phoneId   deviceId    userId
1    23        3434        1235
2    23        5453        235   <---- same phoneId with 1 record 
3    43        5453        2343  <---- same deviceId with 2 record
4    23        3434        6347  <---- same deviceId and phoneID with 1 record

what i need to change is - if phoneId is not unique, set phoneId to userId(from this row). same at deviceId. (if deviceId is not unique, set deviceId to userId) so the final result should be this

id   phoneId   deviceId    userId
1    23        3434        1235
2    235       5453        235   <---- phoneId changed to userId
3    43        2343        2343  <---- phoneId changed to userId
4    6347      6347        6347  <---- phoneId and deviceId changed to userId

The problem is that you can get chains . . . P1 --> D1 --> P2 --> D2. What do you want to do about these? — Gordon Linoff
– Gordon Linoff, Commented Jul 25, 2017 at 14:08

maho · Accepted Answer · 2017-07-25 14:17:39Z

4

Just update duplicated phoneids and then duplicated deviceids (assuming that table name is "t")

UPDATE t SET phoneid=userid FROM (SELECT count(*),phoneid FROM t GROUP BY phoneid HAVING count(*)>1) AS foo WHERE t.phoneid=foo.phoneid;
UPDATE t SET deviceid=userid FROM (SELECT count(*),deviceid FROM t GROUP BY deviceid HAVING count(*)>1) AS foo WHERE t.deviceid=foo.deviceid;

answered Jul 25, 2017 at 14:17

maho

1281 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

update all duplicate rows with different values in pgsql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related