Merge duplicate records into 1 records with the same table and table fields

Question

I have a database table that contains a list of demographic records, some of those participant might have multiple/duplicate records, e.g.

NOTE:
Gender:
119 = Male
118 = Female

Race:
255 = white
253 = Asian

UrbanRural:
331 = Urban
332 = Rural

participantid, gender, race, urbanrural, moduletypeid, hibernateid, and more fields
1, 119, 0, 331, 1, 1, .....
1, 119, 255, 0, 2, 2, .....
1, 0, 255, 331, 3, 3, .....
1, 119, 253, 331, 0, 4, .....

The output should be keep the first hibernateid and the duplicate records will be merge to the first hibernatedid record. If you can do this using function that will check the records if duplicate that would be great, after merged the records it delete the unused duplicate records. Your answer gives me a great idea to resolved this problem. Thanks

Output should be:

participantid, gender, race, urbanrural, moduletypeid, hibernateid, and more fields
1, 119, 255, 331, 1, 1, .....

Help me guys, Thanks

Answer 1

You can do something like this in Postgres 9.1+:

WITH duplicates AS (
  SELECT desired_unique_key, count(*) AS count_of_same_key, min(st.id) AS keep_id, max(st.id) as delete_id
  FROM source_table st
  GROUP BY desired_unique_key
  HAVING count(*) > 1
),
 deleted_dupes AS (
  DELETE FROM source_table st
  WHERE st.id IN (SELECT(delete_id) FROM duplicates)
)
UPDATE source_table st
  SET field = WHATEVER
  FROM duplicates d
  WHERE st.id = d.keep_id