2

I want to update a record if the record exists or insert a new one if it doesn't.

What would be the best approach?

Do a Select Count() and if comes back zero then insert, if one then query the record, modify and update, or should I just try to query the record and catch any system.queryexception?

This is all done in Apex, not from REST or the JS API.

Gerard Sexton
  • 3,114
  • 27
  • 36

3 Answers3

3

Adding to what's already been said here, you want to use FOR UPDATE in these cases to avoid what superfell is referring to. So,

Account theAccount;
Account[] accounts = [SELECT Id FROM Account WHERE Name = 'TEST' LIMIT 1 FOR UPDATE];
if(accounts.size() == 1)
   theAccount = accounts[0];
else
   theAccount = new Account();

// Make modifications to theAccount, which is either:
// 1. A record-locked account that was selected OR
// 2. A new account that was just created with new Account()

upsert theAccount;
Adam
  • 2,595
  • 1
  • 16
  • 20
  • this doesn't work in the case where the account doesn't exist, because there's no account row to lock. you need to lock a parent row that exists in both cases, or use upsert which relies on an underlying database index to do the right thing. – superfell Aug 28 '12 at 20:57
  • ?? Are you sure about that? If you look at my code, I'm creating a new account first. If one doesn't exist then the new account is the one that's used (insert) but if an account does exist, theAccount get's assigned the value and an update is performed. Perhaps my code would be clearer if I made a change to it. Changing it now... – Adam Aug 28 '12 at 21:09
  • yes i'm sure, your revised code uses upsert, and can therefore skip the select to start with. – superfell Aug 28 '12 at 21:36
  • I think I see what you're saying. I personally avoid doing a count() and then a select when possible, because it burns 2 SOQL calls instead of one. My revised code actually does the same thing my previous code did - I just moved the new Account() instantiation to the else block instead of doing it on top. It just looks more clear perhaps, but the upsert was there before. In general, if you're going to update anything that already exists in an object (including upsert), you'll need to SELECT it first. Doing this with FOR UPDATE is the excepted practice, unless I'm missing something. – Adam Aug 29 '12 at 01:21
  • if you are doing an update and you know the new value there is absolutely no need to read the row first. if the new value depends on the current value, then yes you should read the row with FOR UPDATE. – superfell Aug 29 '12 at 01:36
  • True - assuming you already know the Id of the record to update. If you're matching the record on any other key such as Name or some other field(s), you would need to select it first correct? If not, I'd be *very* interested to see the Database.upsert call (or other) that applies a change to a record based on something other than 1 - having the SObject in memory (implying a previous SELECT) or 2 - having the Id of the record (primary key Id). Seriously, that would be really helpful to know, though I would feel a bit embarrassed not already knowing it, if it is possible. Thx!! – Adam Aug 29 '12 at 02:09
1

You should use the upsert call if at all possible, the select then insert/update approach is problematic once you get into the realm of concurrent calls unless you goto the trouble of correctly locking a parent row as part of the select call.

superfell
  • 18,780
  • 4
  • 59
  • 81
0

I would try it with a list and isEmpty() function:

List<Account> a = [select id from account where name = 'blaahhhh' Limit 1];

if(a.isEmpty()){
    System.debug('#### do insert'); 
}
else{
    System.debug('#### do update'); 
}
mast0r
  • 820
  • 5
  • 13