How to do a mass 'Update All'

[clyon]

I need to update a new field on the sys_user table (37K records) . I tried a background script/fix script. Currently it's only running against my id and it's taking forever.

var gr = new GlideRecord('sys_user');

gr.addQuery('user_name', '213015900');

gr.query();

 

gs.print(gr.getRowCount());

 

while(gr.next) {

  //gr.u_opt_in = true;

gr.setValue("u_opt_in", "true");

gr.setWorkflow(false); //Do not run business rules

gr.autoSysFields(false); //Do not update system fields

 

gr.update();

}

I have also tried 'Update All' and even for 1K records it timed out.

How is the safest most efficient way to update 1 field for all user records?

[Michael Ritchie · Accepted solution]

GlideMultipleUpdate is the most efficient way.   I came across this blog entry a few years ago that helps explain its use:

Updating Multiple Entries | Glass 'putan with Service-Now

Because of its age it does use an old package call.   Here is an out of the box example of its use with import sets when you click "reprocess".   Make sure you use the new "GlideMultipleUpdate" API call instead.

var mu = new GlideMultipleUpdate("sys_import_set_row");

  mu.addQuery("sys_import_set", current.sys_id);

  mu.setValue("sys_import_state", "pending");

  mu.execute();

[clyon]

I had to make one change due to an error it was giving me

var mu = new GlideMultipleUpdate("sys_user");

But it worked like a champ!!

Thank you!!

[Mwatkins]

I agree with Michael here, GlideMultipleUpdate is the fastest way to update records. However, here are a couple more considerations:

• GlideMultipleUpdate
  • Is not a documented (i.e. supported) API. You wont find it in the Developer API documentation: https://developer.servicenow.com/app.do#!/search?category=All&v=helsinki&q=glidemultipleupdate&page=.... A supported multiple update API is GlideRecord.updateMultiple(), however, this API is iterative by default and observes all business logic so it is much slower.
  • Does NOT honor the following business logic. In the backend it is doing a SQL to the effect of SET X = Y WHERE A = B. This means that when you use it you will not run any of the following:
    • Business Rules
    • Approval Engine
    • Workflow
    • Cascade Deletes [update Sep 28, 2023: This is irrelevant since Cascade Delete behavior only applies during deletes when a referenced record is  being deleted and the records with fields that reference that record need to be updated. By the way, there is also a GlideMultipleDelete API that works in similar fashion to GlideMultipleUpdate, but it does support Cascade Delete behavior (If you aren't familiar with Cascade Delete behavior, see https://docs.servicenow.com/bundle/vancouver-platform-administration/page/administer/field-administr...) ]
    • Auditing
    • Versioning
    • Updates to the "System" fields (e.g. sys_created_on, sys_mod_count, etc.)
    • ...any other contingent business logic...
  • Doing a group update in MySQL is potentially a locking operation. It can block other select and update operations. For this reason it is important to "batch" GlideMultipleUpdate() on the sys_id field. Because the sys_id field is the primary key of every table it will avoid locking contention in the database.

[update Sep 28, 2023: GlideRecord.updateMultiple will actually use GlideMultipleUpdate() in some cases!! If the table being updated does not meet any of the following criteria then the platform will automatically use GlideMultipleUpdate():

• Table is text indexed
• Table is audited
• SetWorkflow(false) has not been called and table or it's children have any before or after update business rules
• Table has any restricted cascade rule
• Table has any currency fields
• Table has the attribute "iterativeDelete" set true
• Table has the attribute "update_synch" set true
• Table is registered with a Record Watcher
• Table is replicated by IDR
• Operation is against the sys_administrative_script_transaction table ]

[J_Moral]

Also, please remember that GlideMultipleUpdate does NOT have addEncodedQuery function, but if you invoke it, is not going to fail, it will just update the entire table.

updateMultiple  allows turning On/Off the business rules if you use setWorkflow('false'), although even if you use it, is not as quick as GlideMultipleUpdate

Happy coding!