Updating Data in an OTF Table - Teradata Vantage

The UPDATE SQL can be used to update rows in an OTF table. The WHERE clause can be used to update a subset of the rows in the table.

Syntax

UPDATE <datalake_name>.<OTFdatabase_name>.<OTFtable_name> 
    SET ({<column_name> = <value>}[, ...])
    [WHERE <where_clause> | ALL];
<datalake_name> ::= !! name of the data lake object
<OTFdatabase_name> ::= !! name of the database in the OTF catalog
<OTFtable_name> ::= !! name of the OTF table
<database_name> ::= !! Teradata identifier

Example

UPDATE datalake_iceberg_glue.MyDatabase.Tab1 
  SET c1='newvalue',
      c2='newvalue2'
  WHERE country='France';

Usage Considerations

• Expressions with VARCHAR values in write operations are case-sensitive. For example, ..where firstname='MaRk' differs from ..where firstname='Mark' and the evaluation will be case-sensitive. OTF tables are always case-specific (since parquet files are case-sensitive) and there is no workaround for this now.
• FROM clause is not supported.

  UPDATE TDGDW2GF_OTF.lob_bu01_dev01_gdw_demo1.table_demo4 t4
     FROM dbc.TablesV@OTFPOCDEV_QGAccess_fs vce
     SET AttribCol = vce.tablename;
  -- Statement #1 failed: [Teradata Database] [Error 3706] Syntax error: FROM clause not allowed for datalake object.

• The SET clause must be in the simple form of <column_name> = <literal_value>. For example:

  UPDATE... SET c1 = 'abc'

  The following are also supported:

  UPDATE ... SET col    = NULL;
  UPDATE ... SET col_dt = DATE;
  UPDATE ... SET col_t  = CURRENT_TIME;
  UPDATE ... SET col_ts = CURRENT_TIMESTAMP;

• Other expressions like arithmetic or subqueries are not supported in the SET clause.

  UPDATE TDGDW2GF_OTF.lob_bu01_dev01_gdw_demo1.table_demo4 AS t4
     SET AttribCol = (select top 1 tablename from dbc.TablesV@OTFPOCDEV_QGAccess_fs);    
  -- Statement #1 failed: [Teradata Database] [Error 3706] Syntax error: Expressions are disallowed in the SET clause for OTF queries.

• WHERE clause limitations. The following are not supported in WHERE clause.
  • Multiplication and division operators
  • Underscore wildcard in pattern string of LIKE operator (for example: WHERE c1 LIKE 'a_c'). Only the '%' wildcard is allowed and must be in the beginning or end of the LIKE pattern string.
  • Functions like OVERLAPS, LOWER, UPPER, ABS, MOD, EXTRACT, TRIM, and so on.
  • Data type conversions (includes implicit and explicit CAST)
  • Subqueries
• Unsupported Write expressions that are allowed by Read operations:
  • substr
  • like