how to make upsert work instead of insert creating new duplicate record.?

Question

My Object Test__c has two lookup

Emp__c - 1st lookup

Score__c -2nd lookup

I have populated all the value.

upsert (new Test__c(emp__c = 'XXXX',score__c = '123'));
upsert (new Test__c(emp__c = 'XXXX',score__c = '123'));

It create two record ? i guess its sound silly ? But wanna check the clear idea behind the upsert ?

Thanks All....! I just completely understand that it is only based on the ID or External Id. I did an example with my test object. Working awesome...

External_Id__c - Is a text field with the external ID is checked while creating field.

I have a record with the same external id ie. Name = Test1, Exception = Ex1

PAK09_Test1__c ct2 = new PAK09_Test1__c(Name ='Test3',
                                        Exception__c='Ex3',
                                        External_Id__c='123');
database.upsert(ct2, PAK09_Test1__c.Fields.External_Id__c);

Existing record is updated with Name = Test3,Exception = Ex3.

Answer 1

From documentation:

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. Alternatively, you can specify a field to use for matching. For custom objects, specify a custom field marked as external ID. For standard objects, you can specify any field that has the idLookup property set to true. For example, the Email field of Contact or User has the idLookup property set.

Couple of points to remember:

1. If the key is not matched, a new object record is created.

2. If the key is matched once, the existing object record is updated.

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

So it's quite clear how upsert statement works and what are configurable paramters. So in your case it's likely to create two records since there is no any indexed field i.e. id or externalId to detect duplicate records.

Answer 2

yes it creates two records because you calling upsert with new record instance. Upsert uses the ID to determine whether it should create a new record or update an existing one.

for example:

Test__c rec = new Test__c(emp__c = 'XXXX',score__c = '123');
upsert(rec); //inserts  record
rec.score__c = '12345';
upsert(rec); //updates the record

In first upsert(rec), rec does not find any 'id' reference so it insert a new record. For more detail click here

Answer 3

You need an external Id field on your object Test__c to make the upsert work.

Upsert works with either salesforce unique Id or an externalId field, because then only Salesforce can figure out that if you are upserting two duplicate records, then which is the unique key to take into consideration.

The syntax to use upsert with external Id would be:

Upsert list/instance externalKey__c;

Hope this helps :)