9
  1. Given an object Table_A with one unique field Unique and two rows:

    row      Unique
    ---      ------
     1          A
     2          B
    
  2. And the following class:

    public class KeySwapDML {
    
        public void update_resolves () {
    
            Table_A__c[] aList = [select Id, Unique__c
                from Table_A__c];
    
            // Make a new row conflicting with row 2...
            Table_A__c[] upsertable = new Table_A__c[]{};
            upsertable.add( new Table_A__c( Unique__c = 'B' ) );
    
            // Resolve the conflict...
            for ( Table_A__c a : aList ) {
                if ( a.Unique__c == 'B' ) {
                    a.Unique__c = 'C';
                    upsertable.add( a );
                }
            }
            upsert upsertable;
        }
    
    
        public void update_resolves2 () {
    
            Table_A__c[] aList = [select Id, Unique__c
                from Table_A__c];
    
            Table_A__c[] upsertable = new Table_A__c[]{};
    
            for ( Table_A__c a : aList ) {
                if ( a.Unique__c == 'B' ) {
                    a.Unique__c = 'C';
                    upsertable.add( a );
                }
            }
    
            // Try putting the insert in the list after the update...
            upsertable.add( new Table_A__c( Unique__c = 'B' ) );
    
            upsert upsertable;
        }
    
    
        public void update_conflicts () {
    
            Table_A__c[] aList = [select Id, Unique__c
                from Table_A__c];
    
            Table_A__c[] upsertable = new Table_A__c[]{};
            upsertable.add( new Table_A__c( Unique__c = 'C' ) );
    
            // Now produce a conflict...
            for ( Table_A__c a : aList ) {
                if ( a.Unique__c == 'A' ) {
                    a.Unique__c = 'C';
                    upsertable.add( a );
                }
            }
    
            upsert upsertable;
        }
    
    
        public void insert_update () {
    
            Table_A__c[] aList = [select Id, Unique__c
                from Table_A__c];
    
            Table_A__c[] insertable = new Table_A__c[]{};
            insertable.add( new Table_A__c( Unique__c = 'B' ) );
    
            for ( Table_A__c a : aList ) {
                if ( a.Unique__c == 'B' ) {
                    a.Unique__c = 'C';
                }
            }
    
            // Insert prior to resolving the conflict (fails)...
            insert insertable;
            update aList;
        }
    
    
        public void update_insert () {
    
            Table_A__c[] aList = [select Id, Unique__c
                from Table_A__c];
    
            Table_A__c[] insertable = new Table_A__c[]{};
            insertable.add( new Table_A__c( Unique__c = 'B' ) );
    
            for ( Table_A__c a : aList ) {
                if ( a.Unique__c == 'B' ) {
                    a.Unique__c = 'C';
                }
            }
    
            // Insert after resolving the conflict (succeeds)...
            update aList;
            insert insertable;
        }
    }
    
  3. With these results...

    (new KeySwapDML()).update_resolves();
    System.DmlException: Upsert failed. First exception on row 0; first error: DUPLICATE_VALUE, duplicate value found: Unique__c duplicates value on record with id: a03U0000004…
    
    (new KeySwapDML()).update_resolves2();
    System.DmlException: Upsert failed. First exception on row 1; first error: DUPLICATE_VALUE, duplicate value found: Unique__c duplicates value on record with id: a03U0000004…
    
    (new KeySwapDML()).update_conflicts();
    System.DmlException: Upsert failed. First exception on row 1 with id a03U0000004…; first error: DUPLICATE_VALUE, duplicate value found: Unique__c duplicates value on record with id: a03U0000004…
    
    (new KeySwapDML()).insert_update();
    System.DmlException: Insert failed. First exception on row 0; first error: DUPLICATE_VALUE, duplicate value found: Unique__c duplicates value on record with id: a03U0000004…
    
    
    (new KeySwapDML()).update_insert();
    Succeeds
    
  4. Question: is upsert correctly implemented? Its behavior suggests an internal implementation like:

    savepoint
    insert
    validate [ proceed | rollback ]
    update
    validate [ commit | rollback ]
    

I would expect more ACIDity like:

    savepoint
    slamItAllIn
    validate [ commit | rollback ]

In other words, that upsert would at least accept a valid end state, rather than bailing "in the middle."

  • Use case: Imagine Table_A shadows a table from an external system, with field Unique as an external key. Each day a batch runs and you get all changed records from the external table. You select for and assign new object values for the records you find, and create new objects for the ones you don't find. Put it all in a list, hand it to upsert. Works for months, then mysteriously fails...because a key swap occurred in the external system--a completely valid operation on the external system and on your SF model! It fails 'cuz you used upsert instead of update and insert (in that order). – Eric Fournier Dec 27 '13 at 4:34
  • Ignore my example, it's a distraction: If a field in an sObject is changed such that a unique constraint violation--were it saved--is resolved for an added sObject, SF upsert on the sObject list including those sObjects fails, even though the end state is valid. That means upsert is useless for bulk operations on Objects with unique fields. – Eric Fournier Dec 28 '13 at 18:53
  • 1
    this is pretty much standard behavior for any relational db, the index has to be valid after each insert or update, rather than being deffered until commit time. – superfell Dec 29 '13 at 19:50
2

http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_dml_upsert.htm

Use opt_external_id like this :

upsert upsertable Unique__c;

You can also use the Database class which has an extra option "allOrNone" which lets you decide what happens in case of an error on some of the lines :

Database.upsert(upserttable, Unique__c, true);

will be what you are looking for if you want the whole tansaction to fail if there's at least one line in error.

| improve this answer | |
  • Thanks. My contrived example was intended to provide a real-world case for some example context, but it distracted from the real question by implying I was solving a shadow system problem. If a row in the an object (table) is changed such that a unique constraint violation is resolved for an added row, SF upsert on that sObject list including those changes fails, even though the end state is valid. In my scenario, using Unique__c as a lookup key (as you suggest) given its volatility would just corrupt records, so Id was implicitly used as a stable key. – Eric Fournier Dec 28 '13 at 18:42
  • I updated my answer to complete it with another option using Database.Upsert() – altius_rup Dec 29 '13 at 18:22
  • 1
    Can you elaborate? This just causes the conflicting rows not be be inserted, rather than failing the whole call to upsert. How does that help? – Eric Fournier Dec 29 '13 at 19:13
  • Updated my answer – altius_rup Dec 30 '13 at 8:25
  • Thanks for the elaboration, but I'm actually looking for upsert to succeed in this case (since the end state is valid). You given me the option to fail partially or entirely on a call that should be successful. – Eric Fournier Dec 30 '13 at 13:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.