Data Manipulation Language | SQL Fundamentals | VantageCloud Lake - Data Manipulation Language - Teradata Vantage

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 get 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 is similar to the following.

   Name        Salary       Job Title
---------   ---------   -------------
 Newman P    28600.00       Test Tech
   Chin M    38000.00      Controller
Aquilar J    45000.00         Manager
Russell S    65000.00       President
Clement D    38000.00     Salesperson

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

For example, the SELECT statement in the preceding example can be formulated like this:

SELECT   name,   salary,jobtitle
FROM employee;

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 can 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 get 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 follows.

   Name          Salary          Job Title
---------      ----------      -------------
   Chin M        38000.00         Controller
 Greene W        32500.00      Payroll Clerk
Moffitt H        35000.00          Recruiter
 Peters J        25000.00      Payroll Clerk

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

To get 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 optional and the query does not access the table.

The response is one row, similar to 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 statement.

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

If you are performing a bulk insert of rows using INSERT ... SELECT, and you want Vantage to log errors that prevent normal completion of the operation, use the LOGGING ERRORS option. Vantage 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 with the new value. You can also use a WHERE clause to qualify the rows to change.

To update rows in a multirow result table in embedded SQL, declare a cursor for the SELECT statement and use the cursor 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 supports a form of UPDATE called upsert, which is a single SQL statement that includes UPDATE and INSERT functionality. The update operation performs first. If the update operation fails to find a row to update, the insert operation performs.

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.

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 satisfy the matching condition, the merge operation is an update based on the specified WHEN MATCHED THEN UPDATE clause. Otherwise, the merge operation is an 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 Vantage to log errors that prevent normal completion of the operation, use the LOGGING ERRORS option. Database logs errors as error rows in an error logging table that you create with a CREATE ERROR TABLE statement.

Related Information

• DATABASE
• CREATE USER
• MODIFY USER
• CREATE PROFILE