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 .. 140 141 142 143 144 145 < 146 > 147 148 149 150 151 152 .. 241 >> Next


8-120

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

DELETE

Deleting Rows Using a View

Subqueries in DELETE Statements

To delete rows using a view through which the table is accessed:

• The user must have the DELETE privilege on the view. Also, the immediate owner of the view (that is, the database in which the view resides) must have the DELETE privilege on the underlying object (view or base table) whose rows are to be deleted, and the SELECT privilege on all tables that are specified in the WHERE clause.

• Each column of the view must correspond to a column in the underlying table (that is, none of the columns in the view may be derived using an expression).

• No two view columns may reference the same table column.

• The view cannot include a column that contains a range constraint.

• The expression used to define a view cannot have a data type specified for any column in the view.

The condition of a DELETE statement may include subqueries, referencing the delete table and/or other tables.

The implementation of Correlated Subqueries has changed the meaning of some DELETE statements with subqueries.

Example:

DELETE FROM Publisher WHERE 0 =

(SELECT COUNT(*) FROM book WHERE book.PubNum=Publisher.PubNum);

There are two publishers who have books in the library, and two publishers who do not.

In previous releases, the subquery was implemented once, comparing all rows of book with all rows of publisher, and in this instance, finding a count of 2. That count was then compared with 0, for each row of publisher, and the condition was always false.

With the implementation of Correlated Subqueries, the subquery is executed once for each row of the outer reference, Publisher, and since two publishers have no books in the library, two rows of publisher are deleted.

To modify this DELETE to perform as it did in previous releases, change the subquery to include all tables it references in the FROM clause of the subquery.

DELETE FROM Publisher WHERE 0 =

(SELECT COUNT(*) FROM book, publisher WHERE book.PubNum=Publisher.pubnum);

Now, the condition in the subquery has a local defining reference and the statement does not contain a correlated subquery. The count, determined once, is non-zero and no rows are deleted.

Teradata RDBMS for UNIX SQL Reference

8-121
Teradata SQL Syntax Guide

DELETE

Examples

Example 1

The section on Correlated Subqueries in Chapter 9 describes the ruled to specify statements including subqueries that will not provide different responses on this and future releases than were provided by prior releases.

The following examples illustrate the use of DELETE:

To delete rows from the Employee table for employees in Department 500:

DELETE FROM Employee WHERE DeptNo=500;

Example 2

To delete all rows from Employee:

DELETE FROM Employee ALL;

Example 3

To delete the row for employee 10011 from Employee:

DELETE FROM Employee WHERE EmpNo=10011;

Example 4

To delete from Employee rows for employees who work in NYC (this operation joins Employee and Department tables):

DELETE FROM Employee

WHERE Employee.DeptNo = Department.DeptNo AND Department.Location = 'NYC';

Example 5

To delete from Employee rows for managers who have less work experience than their employees (this operation performs a self-join on the Employee table):

DELETE FROM Employee AS Managers

WHERE Managers.DeptNo = Employee.DeptNo

AND Managers.JobTitle IN ('Manager', 'Vice Pres')

AND Employee.YrsExp > Managers.YrsExp;

8-122

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

DELETE

In the following macro, the SELECT and DELETE statements are Example 6 structured as a single multistatement request:

CREATE MACRO ResUse

( FromDate (DATE, DEFAULT DATE),

ToDate (DATE, DEFAULT DATE),

FromTime (INTEGER, DEFAULT 0),

ToTime (INTEGER, DEFAULT 999999),

Proc (VARCHAR(4), DEFAULT 'P'),

Secs (SMALLINT, DEFAULT 600) )

AS ( SELECT

TheDate (TITLE 'Resource//TheDate'),

TheTime (TITLE 'Utilized//TheTime'),

Proc,

AVERAGE(Hits) (TITLE 'Avg//Hits'),

MAXIMUM(Cpu) (TITLE 'Max//CPU'),

AVERAGE(Cpu) (TITLE 'Avg//CPU'),

MAXIMUM(Disk) (TITLE 'Max//Disk'),

AVERAGE(Disk) (TITLE 'Avg//Disk'),

MAXIMUM(Host) (TITLE 'Max//Host'),

AVERAGE(Host) (TITLE 'Avg//Host'),

MAXIMUM(Chan) (TITLE 'Max//Chan'),

AVERAGE(Chan) (TITLE 'Avg//Chan')

FROM DBC.ResUseView

GROUP BY TheDate, TheTime, Proc

WHERE TheDate BETWEEN :FromDate AND :ToDate

AND TheTime BETWEEN :FromTime AND :ToTime

AND Proc CONTAINS :Proc AND Secs EQ :Secs

ORDER BY Proc, TheDate, TheTime;

DELETE FROM ResUseView ALL ; ) ;

In Teradata mode, if the preceding macro was executed, and not within a user-generated transaction, the DELETE statement would be processed using the “fast path” method.

In ANSI mode, the “fast path” method is used (no WHERE clause) when a COMMIT is included in the macro or COMMIT is used at the end of the request line, and immediately following the execution of the macro, within the request.
Previous << 1 .. 140 141 142 143 144 145 < 146 > 147 148 149 150 151 152 .. 241 >> Next