setValues to current record with update multiple

[wollman2]

I want to update all of our sys_user records 'name' fields to remove the middle name. By default, the field calculation on our instance adds the middle name if one exists. Users don't prefer this so I commented that out of the calculated field. But I want to update all existing 200K records to last name, first name. I wanted to know if I can do a fix script to like this:

updateUserDisplayNames();
function updateUserDisplayNames() {
    var query = 'middle_nameISNOTEMPTY';

    var gr = new GlideRecord('sys_user');
    gr.addEncodedQuery(query);
    gr.setValue('name', gr.last_name + ', ' + gr.first_name);
    gr.updateMultiple();
}

I haven't seen any examples of updateMultiple() doing this type of update, so I wanted to validate. I assume I can't because I bet updateMultiple() is not querying each record but just setting specific values like 'false' or specific integer or string.

Any Ideas on how I might speed up this operation?

[Ankur Bawiskar]

Hi,

the above script looks good to me. you can run that. go through below link once before running

https://snprotips.com/blog/2016/12/20/pro-tip-use-updatemultiple-for-maximum-efficiency

Mark Correct if this solves your issue and also mark Helpful if you find my response worthy based on the impact.
Thanks
Ankur

[wollman2]

Thank Ankur.

I had gotten my inspiration from that article to make this more efficient as 200K is a lot of records. That article or the community examples did not mention whether you could use the individual GlideRecord values in for each update multiple. Since the job never completed on my SandBox instance, I may never know. 

I think that is not how updateMultiple() works. I think it is more efficient because it isn't querying every record, but just running an UPDATE SQL query and update that column with a specifically defined value.

[ARG645]

200K records is a Huge count. So it would be better to write a Scheduled Job and run it every 1 Hour or X Hours. And yes, your code will not work as gr.last_name and gr.first_name are unknown entities at the time of execution in the below line fo code. 

gr.setValue('name', gr.last_name + ', ' + gr.first_name);

Modified Code: 

updateUserDisplayNames();
function updateUserDisplayNames() {
    var query = 'middle_nameISNOTEMPTY';

    var gr = new GlideRecord('sys_user');
    gr.addEncodedQuery(query);
    gr.query();
    while(gr.next())
    {
        gr.setValue('name', gr.getValue('last_name') + ', ' + gr.getValue('first_name')); 
        gr.update();
    }
}

[wollman2]

Thanks Aman,

I was trying to use updateMulitple() to be more efficient than looping through 200K records, but your idea of setting a limit and running it through a scheduled job might have been a good idea to reduce load. It turns out just updating the calculated 'sys_user.name' field update all the existing records for me.