I have an external system that needs to update a record in my org. This system only knows the external id, but the problem is that this external id is not unique, e.g. multiple records might have the external id. Now I am changing the record id to be unique, and I want to query the all records created with a duplicate record id.

How can I perform this query?


You could use the HAVING clause for this requirement.

SELECT External__c, count(Id) records FROM MyObject__c
GROUP BY External__c HAVING count(Id) > 1

If you are open to using Apex, you can generate a Set<String> based on the above. If your volume is low enough, you can do it all in one go:

Set<String> externalIds = new Set<String>();
for (AggregateResult aggregate : [
    SELECT External__c FROM MyObject__c
    GROUP BY External__c HAVING count(Id) > 1
]) externalIds.add((String)aggregate.get('External__c'));

List<MyObject__c> duplicates = [
    SELECT Id, External__c FROM MyObject__c
    WHERE External IN :externalIds
  • SELECT External__c, count(Id) records FROM contact GROUP BY External__c HAVING count(Id) > 1,I tried but I am getting error – SFDC Sep 14 '16 at 14:52
  • ELECT external__c, count(Id) records FROM ^ ERROR at Row:1:Column:8 Invalid field: 'external__c' – SFDC Sep 14 '16 at 14:53
  • 3
    You have to replace External__c with the actual API Name of your field... – Adrian Larson Sep 14 '16 at 14:53
  • i want to display all the records , i want above query to add Id or name field for displaying @adrian – SFDC Sep 14 '16 at 15:09
  • 1
    Um. Why do you want to delete all of them? Are you doing so in production? Back up your data. – Adrian Larson Sep 14 '16 at 15:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.