updateMultiple() doesn't update system fields like sys_updated_on or sys_mod_count

[Max Nowak]

Hi,

I'm using GlideRecord updateMultiple() in a usecase where I might have to update >100k records. My problem is that a 3rd party system periodically scans the table via the table API, creates a delta based on sys_updated_on, and moves the data into some sort of data warehouse (it's out of scope for me, I only know that the mechanism relies on sys_updated_on and possibly sys_mod_count).

Due to this circumstance, it's important that, at worst, sys_updated_on gets updated when I update records. The more efficient updateMultiple() doesn't seem to do that, though.

Is there any way I could enable the updating of system fields for updateMultiple(), or, alternatively, is there any reason I simply couldn't update the sys_updated_on manually, like so:

var customTableGr = new GlideRecord('u_custom_table');
customTableGr.addEncodedQuery('active=true');

customTableGr.setValue('u_random_checkbox', true);
customTableGr.setValue('sys_updated_on', new GlideDateTime().getValue());

customTableGr.updateMultple();

Thanks in advance for your help,

Max

[Rachel Gomez]

Unfortunately, it is not recommended to manually update system fields like sys_updated_on as it can cause unexpected behavior in the platform. Instead, you can try using the update() method on each GlideRecord instance in a loop. This will ensure that the system fields are properly updated.

While using update() in a loop can be slower than updateMultiple(), it is still a reliable method that ensures the system fields are updated correctly.

Here's an example:

csharp
Copy code
var customTableGr = new GlideRecord('u_custom_table');
customTableGr.addEncodedQuery('active=true');

customTableGr.query();
while (customTableGr.next()) {
customTableGr.setValue('u_random_checkbox', true);
customTableGr.update();
}
Alternatively, you can also use the setWorkflow(false) method before calling updateMultiple(). This will disable workflow and business rules for the update, allowing the system fields to be updated properly. However, this should be used with caution as it may have unintended consequences on your instance.

Here's an example:

csharp
Copy code
var customTableGr = new GlideRecord('u_custom_table');
customTableGr.addEncodedQuery('active=true');

customTableGr.setValue('u_random_checkbox', true);
customTableGr.setWorkflow(false);
customTableGr.updateMultiple();
It's important to note that the setWorkflow(false) method should only be used in situations where you are confident that disabling workflow and business rules will not cause any issues in your instance.

Regards,

Rachel Gomez

[Mwatkins]

I'm surprised that this isn't documented anywhere else, but it seems this is a little known platform behavior. In almost all cases, GlideRecord.updateMultiple() is going to execute as an iterative update in the background (i.e. 1,000 records updated results in 1,000 UPDATE statements in SQL since we basically just loop over all the matching records and call GlideRecord.update() on them). During the iterative update operation, the business logic will execute that records the sys_ field changes (this is in the Java layer), but there is an exception. If you run GlideRecord.updateMultiple on a table that DOES NOT meet any of the criteria at the bottom, then it will not run as an iterative update, it will run as GlideMultipleUpdate (which I don't think we've ever officially documented) and it will execute a single database operation (i.e. 1,000 records updated results in only 1 UPDATE statement in SQL!). Another side effect of GlideMultipleUpdate is that it doesn't update the sys_ fields unless you explicitly tell it to.

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

[Slava Savitsky]

Hi Matthew,

This is an extremely important and useful piece of information! Thanks a lot for sharing! Is there a possibility to have it reflected in the product documentation?

Regards,

Slava

[Anurag Tripathi]

Sounds like a good thing to put in a blog and post here @Mwatkins