You use DML statements to manipulate and process database values. You can insert new rows into a table, update one or more values in stored rows, or delete a row.
The following table list some basic DML statements. The list is not exhaustive.
| Statement | Action |
|---|---|
| CHECKPOINT | Checkpoints a journal. CHECKPOINT is a statement that defines a recovery point in the journal that can later be used to restore the table contents to its state at a point in time. This can be useful if, for example, the table contents become incorrect due to hardware failure or an operational error. |
| DELETE | Removes a row (or rows) from a table. |
| ECHO | Echoes a string or command to a client. |
| INSERT | Inserts new rows into a table. For more information about a special case of INSERT, see Atomic Upsert later in this table. |
| MERGE | Combines both UPDATE and INSERT in a single SQL statement. Supports primary index operations only, similar to Atomic Upsert but with fewer constraints. |
These statements:
|
Allows you to manage transactions. |
| SELECT | Returns specific row data in the form of a result table. |
| UPDATE | Modifies data in one or more rows of a table. For more information about a special case of UPDATE, see Atomic Upsert later in this table. |
|
Atomic Upsert
The upsert form of the UPDATE DML statement is a Teradata Database extension of the ANSI/ISO SQL standard designed to enhance the performance of TPump utility by allowing the statement to support atomic upsert. For more information about how TPump operates, see Teradata Parallel Data Pump. This feature allows Teradata TPump and all other CLIv2-, ODBC-, and JDBC-based applications to perform single-row upsert operations using an optimally efficient single-pass strategy. This single-pass upsert is called atomic to emphasize that its component UPDATE and INSERT SQL statements are grouped together and performed as a single, or atomic, SQL statement. |