Why does upsert cause a DUPLICATE_VALUE error?

Question

I have a custom object, with a Text Name (ID) field. When I upsert two records with identical Name/ID, but otherwise different values, in two separate upsert statement, one record results, as expected.

When I create a list, add these records to the list, and then upsert the list, I get a DUPLICATE_VALUE error. What gives? By definition, upsert should not do this, should it? Even worse: it doesn't matter whether a record with the identical Name already exists or not, the upsert will fail in both scenarios.

By the way: by default upsert a does not use the Name/ID field as identifying/unique field, as you might expect. You need to do upsert a Name

Complete code:

List<Flight__c> flights = new List<Flight__c>();
Flight__c f1 = new Flight__c();
f1.Name = 'ABC123';
f1.Flight_Number__c = 'ABC';
flights.add(f1);

Flight__c f2 = new Flight__c();
f2.Name = 'ABC123';
f2.Flight_Number__c = '123';
flights.add(f2);

upsert flights Name;

Error message: Line: 12, Column: 1 System.DmlException: Upsert failed. First exception on row 0; first error: DUPLICATE_VALUE, Duplicate external id specified: ABC123: [Name]

Name being the standard identifying field of a custom object. You cannot edit the properties of this field, e.g. making it unique or not unique.

Answer 1

So, if I understood correctly, you are passing the same key twice in the list, correct? If that's the case, this is documented

Upserting Records

If the key is matched multiple times, then an error is generated and the object record is neither inserted or updated.

Answer 2

I think the issue is related to the uniqueness of the 'Name' field. This field is just a user-friendly name for the object that is shown in layouts, related lists, etc. But this field is not guaranteed to be unique.

For instance, you can have 2 contacts with the same First and Last name. It may be erroneous information, but SF will not prevent you from doing that.

Your issue is that by doing

upsert flights Name;

you're telling Salesforce to use the 'Name' field as an external ID. If you just do

upsert flights;

you will not see the error (but this may not be what you want... see below).

From the trailhead lesson on DML:

The upsert statement matches the sObjects with existing records by comparing values of one field. If you don’t specify a field when calling this statement, the upsert statement uses the sObject’s ID to match the sObject with existing records in Salesforce

Suggested solution

If you want an absolutely unique field to identify your records, I would recommend not using the out-of-the-box 'Name' field. Instead, create a custom field (for isntance, 'FlightName') and set the metadata on that field to be External ID and/or Unique.

Answer 3

In your case, you are upserting records into flightsobject by passing Name as the validating factor.

When you do this, you will get 2 records. Because, both the records that you are inserting are having same name as 'ABC123'.

Here Salesforce will not be able to understand which is the actual that needs to be updated. So we will get the exception and records would neither be inserted nor updated.

To avoid this exception, we can specify any unique field as the validating factor for upsert dml statement like below.

Syntax : upsert flights Line_Item_ID__c // In this scenario, Salesforce will search for Line_Item_ID__c in the flights object to validate if the record is there or not.

Answer 4

Guys i got solution for this and it will work suppose object car has brake field which is external Id. Go and make it unique. All the car records you want to UPSERT put it in MAP instead of LIST . Note: this is the trick you have to use. Then simply create another list and add all map records to new list Then do UPSERT of list. It works magically. I to got shocked :P

 map<car> mp =  new map<car>();

  List<car> lt =  new List<car>();

   lt.addAll(mp);

upsert  lt brake;

Answer 5

1) Name field cannot be set as unique or a external id field and if u need to make them unique u need to write custom validations or triggers 2) In your code you are mentioning providing name field to Upsert method and it first checks all the records in your list. Since you have same record twice you are getting an error when you try to upsert both records in tandem. 3) But when you split your code to insert one record at a time upsert doesn't complain as it has not found any duplicates so it goes ahead with the INSERT and your database also doesn't complain as NAME field is not an unique field.

In your case if you want to have same value in name field 'ABC123' in all records i feel you should go for INSERT instead of UPSERT. If you want to have name as unique check point 1. There are many posts in success community on how to make name field unique using validation rules etc. In your case UPSERT is no use as you don't have any external field defined. Main benefit of UPSERT is to update existing records (based on external id field that is also unique) and insert new records if they don't exists. External system sending data into SFDC need not worry abt checking if record exists etc.

https://success.salesforce.com/answers?id=9063A000000DY3AQAW https://help.salesforce.com/articleView?id=managing_duplicates_overview.htm&type=0 depends on your budget.