What should I do if before insert or upsert list must not have two identically equal elements?

2.5K Asked by Aashishchaursiya in Salesforce , Asked on Mar 3, 2023

I had the following exception thrown at me when trying to process more than one selected record via a custom button in the list view.

Class Code

global class MerchandiseInventoryClass {
   webservice static void ConverttoInventory(String[] merchandise_id)
   {
       //Convert Merchandise collection into Inventory Collection...
       Merchandise__c[] merchandise_collection = [SELECT ID,Author__c,Book_Title__c,Edition__c,Publisher__c,Total_Merchandise_QtyTotal_Qty__c,Status__c FROM Merchandise__c WHERE ID = :merchandise_id];

       Inventory__c i = new Inventory__c(); //Line 1
       List newInventory = new List();
       for(Merchandise__c m : merchandise_collection )
       {
           if(m.Status__c !='Fully Converted')
           {
           i.Author__c = m.Author__c ;
           i.Book_Title__c = m.Book_Title__c;
           i.Edition__c = m.Edition__c;
           i.Publisher__c = m.Publisher__c;
           i.Qty_Available__c = m.Total_Merchandise_QtyTotal_Qty__c;
           i.Merchandise__c = m.Id;
           m.Uninventoried_Qty__c = 0;
           m.Status__c = 'Fully Converted';
           newInventory.add(i);
           }
       }
       insert newInventory;
update merchandise_collection;
   }
}

By moving the Line 1 to within the for loop solved the issue for me. Thanks to this post (refer the Best Answer in that post)

https://developer.salesforce.com/forums/ForumsMain?id=906F00000008vFjIAI

What puzzles me is WHY I have to instantiate "i" for every iteration within the loop.

Why is the list collection ("newInventory") complaining that there are duplicate elements and why should it even be confused in the first place when I am performing an "insert" event ?

Answered by Claudine Tippins

before insert or upsert list must not have two identically equal elements-

This line of your code:

Inventory__c i = new Inventory__c();

creates a block of memory that is big enough to hold all the fields of the inventory object and your variable i holds a reference to that memory (its address). As the loop runs, the code changes the values within that block of memory, then this line:

  newInventory.add(i);

adds the same reference value to the list multiple times.

Salesforce made the design choice to flag this condition of multiple identical references by throwing an exception. If they had not, the code would have inserted multiple copies of the object with the fields set to the values of the last iteration round the loop which is not useful and is a harder to detect bug. Moving the new inside the loop yields the correct code with a separate block of memory for each inventory object and independent values for the fields.

Your Answer

Answer (1)

Ranjana

If you encounter the error "before insert or upsert, the list must not have two identically equal elements," it means that your data contains duplicate values that violate a unique constraint. Here’s how you can handle this issue:

1. Identify the Duplicate Entries

Before inserting or updating, check if your list contains duplicates.
Use a query to find duplicates in the database:

SELECT column_name, COUNT(*) 

FROM table_name 

GROUP BY column_name 

HAVING COUNT(*) > 1;

2. Remove Duplicates Before Insert/Upsert

If working with SQL, use DISTINCT:

INSERT INTO table_name (column1, column2)

SELECT DISTINCT column1, column2 FROM source_table;

In Python, use set() to filter unique values:

  unique_list = list(set(your_list))

3. Use ON CONFLICT (PostgreSQL) or ON DUPLICATE KEY UPDATE (MySQL)

PostgreSQL:

INSERT INTO table_name (id, name) 

VALUES ('123', 'John') 

ON CONFLICT (id) DO NOTHING;

MySQL:

  INSERT INTO table_name (id, name) VALUES ('123', 'John')ON DUPLICATE KEY UPDATE name = VALUES(name);

4. Validate Data Before Upsert

Ensure your application checks for duplicates before sending data.
In Python, filter out duplicates with Pandas:

  df.drop_duplicates(inplace=True)

5. Check Unique Constraints

If the error persists, check your database schema to ensure proper unique indexes exist.

3 Weeks