Is it possible to write a CAML query that will update or insert an entry based on its existence?

Question

I'm working with Sharepoint using CSOM and Javascript. I need to cache usage statistics in a List with one entry per user, updating values in a user's list entry when they interact with the site in a particular manner.

I'm wondering if it's possible to write a CAML query that will be smart enough to create an entry if a particular user doesn't already exist in the list, but also knows to update an existing entry instead if the user already has one going? I'm thinking something similar to this:

INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE 
name=VALUES(name), age=VALUES(age)

If CAML can't really handle something like this, am I restricted to making two async calls, one to verify the existence of a user in the cache list, and another to either create or update an entry?

Answer 1

CAML doesn't have anything built in for that, so you are limited to making two async calls with the JavaScript client object model.

The code snippet below checks a list called "Training Progress" for an item created by the current user. It updates it if it exists or creates it if it doesn't exist.

ExecuteOrDelayUntilScriptLoaded(
function(){
    var numRight = 12; /* dummy data */
    var clientContext = new SP.ClientContext.get_current();
    var list = clientContext.get_web().get_lists().getByTitle("Training Progress");
    var camlQuery = new SP.CamlQuery();
    camlQuery.set_viewXml("<View><Query><Where><Eq><FieldRef Name=\"Author\" LookupId=\"TRUE\"/><Value Type=\"Lookup\">" + _spUserId + "</Value></Eq></Eq></Where></Query><RowLimit>1</RowLimit></View>");
    var currentProgress = list.getItems(camlQuery);
    clientContext.load(currentProgress);
    clientContext.executeQueryAsync(Function.createDelegate(this,
        function(){ /* List exists, grab the user's item if it exists */
        var itemEnumerator = currentProgress.getEnumerator();
        var itemId = -1;
        while(itemEnumerator.moveNext()){ 
            var item = itemEnumerator.get_current();
            itemId = item.get_item("ID");
        }
        if(itemId == -1){ /* Create item in list */
            var itemCreateInfo = new SP.ListItemCreationInformation();
            var newItem = list.addItem(itemCreateInfo);
            newItem.set_item('Title',_spUserId);
            newItem.set_item('Certification',numRight);
            newItem.update();
            clientContext.load(newItem);
        }else{ /* Update item in list */
            var item = list.getItemById(itemId);
            item.set_item('Certification',numRight);
            item.update();
            clientContext.load(item);
        }
        clientContext.executeQueryAsync();
    }),Function.createDelegate(this,
        function(sender,args){ /* List doesn't exist. */
                alert(args.get_message());
    }));
},"SP.JS");

You can even take this a step further and create the list if it doesn't exist, but that assumes the code is running under the context of someone with access to create lists.