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.
- 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, etc.
- Data type conversions (includes implicit and explicit CAST)
- Subqueries
- Unsupported write expressions that are allowed by Read operations:
- substr
- like