Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

Teradata RDBMS forUNIX SQL Reference - NCR

NCR Teradata RDBMS forUNIX SQL Reference - NCR, 1997. - 913 p.
Download (direct link): teradataforunix1997.pdf
Previous << 1 .. 139 140 141 142 143 144 < 145 > 146 147 148 149 150 151 .. 241 >> Next


This form of DELETE is used if the condition directly references fields in tables other than the one that rows are to be deleted from, that is, if the condition included a Subquery or references a Derived Table.

This form is flagged as non-ANSI when the SQL flagger is enabled. It is a Teradata extension to ANSI SQL.

DELETE—Basic Form:

DELETE-,--------tname

Lfrom-I

aname-I - WHERE - condition -
LssJ
ALL

CT

FF07B072

DELETE with implied join:

DELETE—del tname -

Urom-I

(A>T 0

tname-

a

e

<A)

<B)

y- - WHERE— condition - CF*
ALL

FF07R078

Teradata RDBMS for UNIX SQL Reference

8-117
Teradata SQL Syntax Guide

DELETE

where:

Syntax Element. . . . Specifies . . .
tname the table the DELETE is to remove rows from. Used when the DELETE includes a condition that directly references more than one table.
FROM tname the name of the table to delete rows from (Basic DELETE). This phrase is optional for the Basic DELETE statement.
AS an optional keyword introducing the alias name of the table to be deleted from.
aname an optional alias name, used for self-joins.
del_tname the name of the table to be deleted from.
FROM list the list of tables and views referenced in the condition. Elements of the list are tnames with optional alias anames (derived table DELETE).
WHERE n introduction to a conditional clause. See also Chapter 7, “Queries: The SELECT Statement,” “WHERE Clause”.
condition the conditional expression to be used for determining rows to be deleted. The expression operands can be constants, or references to fields in the specified table or other tables. The conditional also may specify an embedded SELECT operation.
ALL that all rows to be deleted, resulting in an empty table. This is the default and is used when a WHERE condition is not specified. The ALL option is flagged as non ANSI, when the SQL flagger is enabled; it is a Teradata extension to ANSI SQL.

To delete rows from a table, the user must have the DELETE

Privileges Required privilege on the table.

If the DELETE statement is a ‘searched’ DELETE, i.e., it includes the WHERE condition, the user must also have SELECT privilege on all tables accessed for checking the condition.

Use caution when granting the privilege to delete data through a view. Data in fields that might not be known to the user, is also deleted when a row is deleted through a view.

A DELETE operation sets a Write lock for the table or row.

Locks

8-118

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

DELETE

Rules for Using DELETE

Unconstrained DELETE Processing Time (‘Fast Path’)

The following rules apply to using DELETE.

• ‘Tablename’ (tname) has been added before the FROM clause, to specify the deleted table name if the syntax for DELETE with an implicit join is used.

• All alias names must be defined in the FROM clause.

• The activity count in the success response for a DELETE statement reflects the total number of rows deleted.

The abbreviation for the DELETE statement is DEL.

When submitted by itself as a transaction, an unconstrained delete (DELETE...ALL) is processed very quickly.

An unconstrained DELETE is one where no conditions are attached, i.e, all rows are deleted. When an unconstrained delete is submitted as part of a multistatement transaction, the speed with which it is processed depends on its position in the transaction.

To enable “fast path” processing, which defers processing until end transaction time and allows rollback processing to be almost instantaneous, positioning must be as follows, depending on the mode being used.

Teradata RDBMS for UNIX SQL Reference

8-119
Teradata SQL Syntax Guide

DELETE

IN this mode . . . FOR this type of transaction . . . Positioning must be as follows . . .
ANSI All The unconditional DELETE must be immediately followed by a COMMIT. For example, DELETE FROM tname ALL;COMMIT;
Teradata Implicit (such as a multistatement macro or BTEQ request) DELETE...ALL must be the last statement in the request.
Explicit DELETE...ALL must be immediately followed by the END TRANSACTION statement that terminates the currently open transaction. This implementation is not available to a Teradata Preprocessor2 application, because the DELETE and END TRANSACTION statements must fall into the same request packet.

Processing time for a constrained delete (DELETE...WHERE) may be longer when:

• The FALLBACK option is specified for the table, because the rows in the secondary copy of the table must also be deleted.

• Non-unique secondary indexes are defined for the table, because the secondary index subtable(s) also must be updated to account for the rows deleted.

Processing time for a constrained delete may be shortened by specifying a primary index value or a unique secondary index value as the conditional expression of the WHERE clause.

Constrained DELETE Processing Time (‘Slow Path’)
Previous << 1 .. 139 140 141 142 143 144 < 145 > 146 147 148 149 150 151 .. 241 >> Next