function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
FuguSailorFuguSailor 

Self Join in SOQL to find duplicates

In standard SQL I used to create self joins to find duplicate entries where you would have a field like Name, that should be unique, but because there is a composite key, but one of the values is not correct. Doing this I could write an SQL query that would be something like:

 

SELECT T1.NAME, T1.FIELD_1, T2.NAME, T2.FIELD_1

FROM MYTABLE AS T1, MYDABLE AS T2

WHERE T1.NAME = T2.NAME AND T1.FIELD_1 <> T2.FIELD_2

 

How would I do this in SOQL?

Best Answer chosen by Admin (Salesforce Developers) 
Cory CowgillCory Cowgill

That may not be possible in a single SOQL query. As I said, SOQL is really a subset of SQL with some nuances. Almost like HQL really.

 

You may have to do the query I showed above, then iterate over your result set with a map or similar data structure to achieve the use case you are trying to do.

All Answers

Cory CowgillCory Cowgill

SOQL is not he same as SQL due to the multi-tenant nature of the platform. You can't do JOINS like you would do in SQL, you can do Relationship Queries.

 

You can detect duplicates using HAVING clause. Not exactly like the query you have below I know.

 

Select Name, Count(Id) from Account Group By Name Having Count > 1

 

http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_soql_select_having.htm

 

http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_soql_relationships.htm

 

Hope that helps.

FuguSailorFuguSailor

That will tell me which ones have duplicates but it doesn't help with showing me the duplicate records in the same query so that I can decide which one needs to be deleted. Rather than creating a listing of just which ones have duplicates and then having to then query one by one to see which one is valid, I'd rather have it show me the first record and any duplicates with key data so I can decide from one query, which ones to remove. I've done this before in SQL against SQL Server and, that time, it turned out to be an eye opener.

Cory CowgillCory Cowgill

That may not be possible in a single SOQL query. As I said, SOQL is really a subset of SQL with some nuances. Almost like HQL really.

 

You may have to do the query I showed above, then iterate over your result set with a map or similar data structure to achieve the use case you are trying to do.

This was selected as the best answer