We want to replace all NULL values within a certain table with the default value specified for the according column. And we don't want to create the necessary update statements manually.
A small metadata-driven procedure script (Lua) that issues the required update statements. (See Attachment)
performs a rollback after the metadata request, to avoid a read-write conflict scenario.
performs all updates within a single transaction.
willnot abortwhen an update on one of the columns fails.
performs a commit when all columns have been handled, regardless of any errors encountered.
...on column selection
The scriptincludesall columns that do have a DEFAULT value set. Itexcludesall columns with a NOT NULL constraint (ignoring the actual state of the constraint). Obviously, such a column can not contain any NULL values that need updating.
...on row selection
Due to Exasol's memory management and data processing, the script handles each column separately. This minimizes both the amount of memory required for processing and the amount of data blocks being written. The script doesnotcontain any delta functionality, it will process all rows of the table each time it is called.
Just create the script in any schema you like (CREATE SCRIPT permission required). It does not have any dependencies.