Timothy Leavitt · Nov 9, 2021

SQL update with %IGNOREINDEX

I'm in a tricky situation where a new required property is being added to a class, along with an index on it. The existing data has nulls, so the index build fails. I can't run a query to update the rows where there are nulls, because it tries to use the index, which hasn't been built yet.

I realize that a valid workaround would be to remove the index, add the property, run the query to do the update, then re-add the index, but this feels overly messy (and doesn't fit well with my logic for incremental deployment). It would be nice if there was instead some way to make the update not use the index, but I'm not seeing that in the documentation. Any thoughts?

Product version: IRIS 2020.1
0 137
Discussion (3)1
Log in or sign up to continue

Ended up being a simpler solution than expected... the query had been

update table set newfield = 1 where newfield is null

and really should have just been

update %NOINDEX table set newfield = 1

(because it was null everywhere).