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