15.00 - Data Manipulation Language - Teradata Database

Teradata Database SQL Fundamentals

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1141-015K

Data Manipulation Language

The SQL Data Manipulation Language (DML) is a subset of the SQL language and consists of all SQL statements that support the manipulation or processing of database objects.

Selecting Columns

The SELECT statement returns information from the tables in a relational database. SELECT specifies the table columns from which to obtain the data, the corresponding database (if not defined by default), and the table (or tables) to be accessed within that database.

For example, to request the data from the name, salary, and jobtitle columns of the Employee table, type:

   SELECT name, salary, jobtitle FROM employee ;

The response might be something like the following results table.

 

Name

Salary

JobTitle

Newman P

28600.00

Test Tech

Chin M

38000.00

Controller

Aquilar J

45000.00

Manager

Russell S

65000.00

President

Clements D

38000.00

Salesperson

Note: The left‑to‑right order of the columns in a result table is determined by the order in which the column names are entered in the SELECT statement. Columns in a relational table are not ordered logically.

As long as a statement is otherwise constructed properly, the spacing between statement elements is not important as long as at least one pad character separates each element that is not otherwise separated from the next.

For example, the SELECT statement in the above example could just as well be formulated like this:

   SELECT   name,   salary,jobtitle
   FROM employee; 

Notice that there are multiple pad characters between most of the elements and that a comma only (with no pad characters) separates column name salary from column name jobtitle.

To select all the data in the employee table, you could enter the following SELECT statement:

   SELECT * FROM employee ; 

The asterisk specifies that the data in all columns (except system-derived columns) of the table is to be returned.

Selecting Rows

The SELECT statement retrieves stored data from a table. All rows, specified rows, or specific columns of all or specified rows can be retrieved. When used in a subquery, the SELECT statement can also select rows from a derived table or view.

The FROM, WHERE, ORDER BY, DISTINCT, WITH, GROUP BY, HAVING, and TOP clauses provide for a fine detail of selection criteria.

To obtain data from specific rows of a table, use the WHERE clause of the SELECT statement. That portion of the clause following the keyword WHERE causes a search for rows that satisfy the condition specified.

For example, to get the name, salary, and title of each employee in Department 100, use the WHERE clause:

   SELECT name, salary, jobtitle FROM employee 
      WHERE deptno = 100 ;

The response appears in the following table.

 

Name

Salary

JobTitle

Chin M

38000.00

Controller

Greene W

32500.00

Payroll Clerk

Moffit H

35000.00

Recruiter

Peterson J

25000.00

Payroll Clerk

To obtain data from a multirow result table in embedded SQL, declare a cursor for the SELECT statement and use it to fetch individual result rows for processing.

To obtain data from the row with the oldest timestamp value in a queue table, use the SELECT AND CONSUME statement, which also deletes the row from the queue table.

Zero-Table SELECT

Zero-table SELECT statements return data but do not access tables.

For example, the following SELECT statement specifies an expression after the SELECT keyword that does not require a column reference or FROM clause:

   SELECT 40000.00 / 52.;

The response is one row:

   (40000.00/52.)
   -----------------
              769.23

Here is another example that specifies an attribute function after the SELECT keyword:

   SELECT TYPE(sales_table.region);

Because the argument to the TYPE function is a column reference that specifies the table name, a FROM clause is not required and the query does not access the table.

The response is one row that might be something like the following:

   Type(region)
   ---------------------------------------
   INTEGER

Adding Rows

To add a new row to a table, use the INSERT statement. To perform a bulk insert of rows by retrieving the new row data from another table, use the INSERT … SELECT form of the statement.

Defaults and constraints defined by the CREATE TABLE statement affect an insert operation in the following ways.

 

WHEN an INSERT statement …

THEN the system …

attempts to add a duplicate row

  • for any unique index
  • to a table defined as SET (not to allow duplicate rows)
  • returns an error, with one exception. The system silently ignores duplicate rows that an INSERT … SELECT would create when the:

  • table is defined as SET
  • mode is Teradata
  • omits a value for a column for which a default value is defined

    stores the default value for that column.

    omits a value for a column for which both of the following statements are true:

  • NOT NULL is specified
  • no default is specified
  • rejects the operation and returns an error message.

    supplies a value that does not satisfy the constraints specified for a column or violates some defined constraint on a column or columns

    If you are performing a bulk insert of rows using INSERT … SELECT, and you want Teradata Database to log errors that prevent normal completion of the operation, use the LOGGING ERRORS option. Teradata Database logs errors as error rows in an error logging table that you create with a CREATE ERROR TABLE statement.

    Updating Rows

    To modify data in one or more rows of a table, use the UPDATE statement. In the UPDATE statement, you specify the column name of the data to be modified along with the new value. You can also use a WHERE clause to qualify the rows to change.

    Attributes specified in the CREATE TABLE statement affect an update operation in the following ways:

  • When an update supplies a value that violates some defined constraint on a column or columns, the update operation is rejected and an error message is returned.
  • When an update supplies the value NULL and a NULL is allowed, any existing data is removed from the column.
  • If the result of an UPDATE will violate uniqueness constraints or create a duplicate row in a table which does not allow duplicate rows, an error message is returned.
  • To update rows in a multirow result table in embedded SQL, declare a cursor for the SELECT statement and use it to fetch individual result rows for processing, then use a WHERE CURRENT OF clause in a positioned UPDATE statement to update the selected rows.

    Teradata Database supports a special form of UPDATE, called the upsert form, which is a single SQL statement that includes both UPDATE and INSERT functionality. The specified update operation performs first, and if it fails to find a row to update, then the specified insert operation performs automatically. See, however, “Merging Rows” on page 118.

    Deleting Rows

    The DELETE statement allows you to remove an entire row or rows from a table. A WHERE clause qualifies the rows that are to be deleted.

    For the way to delete rows in a multirow result table in embedded SQL, see SQL Stored Procedures and Embedded SQL.

    Merging Rows

    The MERGE statement merges a source row set into a target table based on whether any target rows satisfy a specified matching condition with the source row. The MERGE statement is a single SQL statement that includes both UPDATE and INSERT functionality.

     

    IF the source and target rows …

    THEN the merge operation is an …

    satisfy the matching condition

    update based on the specified WHEN MATCHED THEN UPDATE clause.

    do not satisfy the matching condition

    insert based on the specified WHEN NOT MATCHED THEN INSERT clause.

    If you are performing a bulk insert or update of rows using MERGE, and you want Teradata Database to log errors that prevent normal completion of the operation, use the LOGGING ERRORS option. Teradata Database logs errors as error rows in an error logging table that you create with a CREATE ERROR TABLE statement.

    Related Topics

    For details about selecting, adding, updating, deleting, and merging rows, see SQL Data Manipulation Language.