function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Michael MMichael M 

How to write SOQL query to find duplicate person accounts

Hello, I need to find all of our duplicate person accounts. Specifically, I want to find all Person Accounts where EITHER:
1) Name AND Date of Birth (custom field) match, OR
2) Custom ID Number (custom field) matches.

How would I write both of those SOQL queries? 
Best Answer chosen by Michael M
AnudeepAnudeep (Salesforce Developers) 
Hi Michael - The SOQL query provided will find Duplicate Person Account by Name. Meaning all the duplicate accounts that have the matching name will be returned. If you want to check for all person accounts that have two fields matching, then I think you need to group by those two fields
  
SELECT Count(ID) 
FROM Account 
WHERE IsPersonAccount=TRUE
GROUP BY Name, matching_dob__c
HAVING Count(ID) > 1 
ORDER BY Count(ID) DESC

I haven't tried this myself so I am unsure if it will work but here is a post explaining how the results will be returned when we want to group two fields 
 

All Answers

AnudeepAnudeep (Salesforce Developers) 
Hi Michael, 

The query usually looks like this, replace the exact API name of the field that you want to match with along side of GROUP BY 
  
SELECT Count(ID), PersonEmail 
FROM Account 
WHERE IsPersonAccount = TRUE
GROUP BY Name 
HAVING Count(ID) > 1 
ORDER BY Count(ID) DESC 
LIMIT 2000

Let me know if it helps

Anudeep
Michael MMichael M
Hi Anudeep, thank you! In the example you gave, will it only check for duplicate names? 
I need to check for all Person Accounts that have 2 of the same field: matching name AND matching DOB.. How would I write that?
AnudeepAnudeep (Salesforce Developers) 
Hi Michael - The SOQL query provided will find Duplicate Person Account by Name. Meaning all the duplicate accounts that have the matching name will be returned. If you want to check for all person accounts that have two fields matching, then I think you need to group by those two fields
  
SELECT Count(ID) 
FROM Account 
WHERE IsPersonAccount=TRUE
GROUP BY Name, matching_dob__c
HAVING Count(ID) > 1 
ORDER BY Count(ID) DESC

I haven't tried this myself so I am unsure if it will work but here is a post explaining how the results will be returned when we want to group two fields 
 
This was selected as the best answer
Michael MMichael M
Thank you, Anudeep. That seemed to work. Now, I need to combine all of the duplicates-- is there a best way to do that?
AnudeepAnudeep (Salesforce Developers) 
Hi Michael, 

The best way to do is to use Database.merge. I recommend reviewing the example from the documentation

If you find any of the information I shared helpful, please mark this answer as Best. Thank You!

Anudeep
Michael MMichael M
Thank you very much
Michael MMichael M
Hi Anudeep, as a side question, is there a way I can run the query without a limit of 2000? I have more than 2000 duplicates, and I want to see all of them in one big list. Is this possible somehow in workbench, or otherwise?