Uploaded image for project: 'Solution Center'
  1. Solution Center
  2. SOL-585

Update all NULL values with column default

    Details

    • Type: How To
    • Status: Published
    • Affects Version/s: EXASOL 6.0.0, Exasol 6.1.0, EXASolution 5.0.0, Exasol 6.2.x
    • Fix Version/s: None
    • Component/s: EXASolution
    • Labels:
      None
    • Solution:
      Hide

      Problem

      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.

      Solution

      A small metadata-driven procedure script (Lua) that issues the required update statements.
      (See Attachment)

      Notes...

      ...on transactions

      The script

      • performs a rollback after the metadata request, to avoid a read-write conflict scenario.
      • performs all updates within a single transaction.
      • will not abort when 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 script includes all columns that do have a DEFAULT value set.
      It excludes all 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 does not contain any delta functionality, it will process all rows of the table each time it is called.

      Installation

      Just create the script in any schema you like (CREATE SCRIPT permission required). It does not have any dependencies.

      create or replace /* procedure */ script REPLACE_NULL( schema_name, table_name )
      ...
      

      Usage

      When calling the script, it expects two parameters: A schema name and a table name:

      execute script REPLACE_NULL( 'my schema', 'my table' );
      

      Both schema and table name are expected as string and will be case-sensitive.

      Example

      open schema SR9000;
      -- Rows affected: 0
      
      create table Invoice( invoice_id int, invoice_date date default date '2017-01-01' );
      -- Rows affected: 0
      
      insert into Invoice values (1, null), (2, null), (3, '2017-02-01');
      -- Rows affected: 3
      
      execute script REPLACE_NULL( 'SR9000', 'Invoice' );
      -- [43000] "No columns found for "SR9000"."Invoice"" caught in script "SR9000"."REPLACE_NULL" at line 23 (Session: 1585944483210400591)
      

      ... yes. We created the table using a regular identifier, so it ended up as uppercase...

      execute script REPLACE_NULL( 'SR9000', 'INVOICE' );
      

      This returns:

      COLUMN_NAME COLUMN_TYPE COLUMN_DEFAULT UPDATE_RESULT
      INVOICE_DATE DATE TO_DATE('2017-01-01','YYYY-MM-DD') 2 rows updated
      Show
      Problem 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. Solution A small metadata-driven procedure script (Lua) that issues the required update statements. (See Attachment) Notes... ...on transactions The script performs a rollback after the metadata request, to avoid a read-write conflict scenario. performs all updates within a single transaction. will not abort when 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 script includes all columns that do have a DEFAULT value set. It excludes all 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 does not contain any delta functionality, it will process all rows of the table each time it is called. Installation Just create the script in any schema you like (CREATE SCRIPT permission required). It does not have any dependencies. create or replace /* procedure */ script REPLACE_NULL( schema_name , table_name ) ... Usage When calling the script, it expects two parameters: A schema name and a table name: execute script REPLACE_NULL( 'my schema ' , 'my table ' ); Both schema and table name are expected as string and will be case-sensitive. Example open schema SR9000; -- Rows affected: 0 create table Invoice( invoice_id int , invoice_date date default date '2017-01-01' ); -- Rows affected: 0 insert into Invoice values (1, null ), (2, null ), (3, '2017-02-01' ); -- Rows affected: 3 execute script REPLACE_NULL( 'SR9000' , 'Invoice' ); -- [43000] " No columns found for " SR9000 "." Invoice "" caught in script " SR9000 "." REPLACE_NULL" at line 23 ( Session : 1585944483210400591) ... yes. We created the table using a regular identifier, so it ended up as uppercase... execute script REPLACE_NULL( 'SR9000' , 'INVOICE' ); This returns: COLUMN_NAME COLUMN_TYPE COLUMN_DEFAULT UPDATE_RESULT INVOICE_DATE DATE TO_DATE('2017-01-01','YYYY-MM-DD') 2 rows updated
    • Category 1:
      SQL - Data types
    • Category 2:
      Scripting

      Attachments

        Activity

          People

          • Assignee:
            CaptainEXA Captain EXASOL
            Reporter:
            CaptainEXA Captain EXASOL
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated: