The DELETE FROM SQL can be used to delete rows from an OTF table. The WHERE clause can be used to delete a subset of the rows in the table.
Syntax
DELETE FROM <datalake_name>.<OTFdatabase_name>.<OTFtable_name> [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
DELETE FROM datalake_glue.MyDatabase.Tab1 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