You need to sign in to do that
Don't have an account?
FuguSailor
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?
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
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.
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.
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.