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

So you want a query to find/remove duplicates, is that right?

If so, try this:

SELECT T1.* FROM table_name T1, table_name T2
WHERE T1.dupe_field = T2.dupe_field
AND T1.other_dupe_field = T2.other_dupe_field
AND T1.primary_key > T2.primary_key;

Change the table and field names to suit your own table structure.

Confirm with this SELECT query that it is selecting the dupes you want to remove, and then change it to a DELETE in order to remove the dupes.

Answer 2

Try something like:

select participantid, min(gender), min(race), min(urbanrural), 
min(case moduletypeid when 0 then null else moduletypeid end), min(hibernateid), ...
from yourtable
group by participantid

It's not clear to me why moduletypeid whould be returned as 1 in your example - I have assumed that 0 in this field is a special case, to be treated as null (hence the case clause).