Data Manipulation Language Statements - Analytics Database - Teradata Vantage

Database Introduction

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-09-27
dita:mapPath
gtm1628096154303.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
dsm1472253642401
lifecycle
latest
Product Category
Teradata Vantageā„¢

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.

That is, defines a recovery point in the journal that can later be used to restore the table contents to its state at that time.

Useful if table contents become incorrect due to hardware failure or operational error.

DELETE Removes one ore more rows from a table.
ECHO Echoes a string or command to a client.
INSERT Inserts one ore more new rows into a table.

For information about a special case of INSERT, see Atomic Upsert later in this table.

MERGE Combines UPDATE and INSERT in a single SQL statement. Supports primary index operations only, similar to Atomic Upsert but with fewer constraints.
  • ABORT
  • ROLLBACK
  • COMMIT
  • BEGIN TRANSACTION
  • END TRANSACTION
Enable you to manage transactions.
SELECT Returns specific row data as a result table.
UPDATE Modifies data in one or more rows of a table.
Atomic Upsert

This form of the UPDATE statement is a database extension of the ANSI/ISO SQL standard, designed to enhance the performance of the TPump utility. For more information about how TPump operates, see Teradata Parallel Data Pump.

Atomic Upsert 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 because its component UPDATE and INSERT SQL statements are performed as a single SQL statement.