15.00 - 15.10 - Data Manipulation Language Statements - Teradata Database

Teradata Database Introduction to Teradata

Product
Teradata Database
Release Number
15.00
15.10
Content Type
User Guide
Publication ID
B035-1091-151K
Language
English (United States)
Last Update
2018-09-25

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:

  • ABORT
  • ROLLBACK
  • COMMIT
  • BEGIN TRANSACTION
  • END TRANSACTION
  • 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” on page 173.

    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.