Updating Data in an OTF Table - Teradata Vantage

Apache Iceberg and Delta Lake Open Table Format on VantageCloud Lake Getting Started

Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
December 2024
ft:locale
en-US
ft:lastEdition
2025-01-03
dita:mapPath
bsr1702324250454.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
bsr1702324250454

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 at the moment.

Usage Considerations: Iceberg

  • Unsupported write expressions that are allowed by Read operations:
    • substr
    • like
  • The following are not supported:
    • Writing ORC data files.
    • Writing to a BLOB column