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.
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.
|Newman P||28600.00||Test Tech|
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.
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.
|Greene W||32500.00||Payroll Clerk|
|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 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:
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
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
||returns an error, with one exception. The system silently ignores duplicate rows that an INSERT ... SELECT would create when the:
|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:
||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||rejects the operation and returns an error message.|
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. Database logs errors as error rows in an error logging table that you create with a CREATE ERROR TABLE statement.
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.
- 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 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.
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.
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 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.
- Selecting, adding, updating, deleting, and merging rows, see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.
- The DATABASE, CREATE USER, MODIFY USER, or using the CREATE PROFILE statement to define the default database for member users, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
- For the way to delete rows in a multirow result table in embedded SQL, see Teradata Vantage™ - SQL Stored Procedures and Embedded SQL, B035-1148.