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

Teradata RDBMS forUNIX SQL Reference - NCR

NCR Teradata RDBMS forUNIX SQL Reference - NCR, 1997. - 913 p.
Download (direct link): teradataforunix1997.pdf
Previous << 1 .. 178 179 180 181 182 183 < 184 > 185 186 187 188 189 190 .. 241 >> Next

• The operation attempts to insert a character string into a VARCHAR field, and that operation causes the row to become identical to another row (except for the number of trailing blanks), for a table not permitting duplicate rows.

• If in ANSI mode, updating character data, where in order to comply with maximum length of the target column, non-blank characters are truncated from the source data.

In Teradata mode, the above update is permitted.

• The operation attempts to update a row by using values that will create a duplicate of an existing row, for a table not allowing duplicate rows.

See also the “Examples” section below.

For a Japanese character site, an error is returned if the operation

UPDATE at a Japanese attempts to update a field with:

Character Site

• a value containing multibyte characters that are not valid for the current character set.

• a value containing multibyte characters that result in an odd number of bytes.

• graphic data that is greater than 16000 logical characters (one-half of maxlength) or greater than maxlength.

An error message is not returned if an UPDATE operation attempts to update a field using a graphic or character string that is longer or shorter than that declared for the column.

When an UPDATE statement specifies a join operation, the join is UPDATES and Joins more efficient if the WHERE condition uses values for columns in

the joined tables on which primary or secondary indexes are defined.

Refer to Chapter 7, “Queries: The SELECT Statement,” “WHERE Clause” for a discussion of using indexed values to specify a join condition.

The optional FROM list included in the UPDATE syntax, is a non-FROM List ANSI extension provided to support correlated subqueries and

derived tables in the search conditions for UPDATE.

The FROM list should be used only to provide the outer scope of a table with fields referenced in a subquery, making the subquery a correlated subquery, or to allow reference to a derived table.

Teradata RDBMS for UNIX SQL Reference

Teradata SQL Syntax Guide


Subqueries in UPDATE Statements


Example 1

If a table is listed in the FROM for the UPDATE, and not in the FROM for a subquery, then field references in the subquery are scoped at the outer level, making it a correlated subquery.

The condition of an UPDATE statement may include subqueries, referencing the update table and/or other tables.

The implementation of correlated subqueries has changed the meaning of some UPDATE statements with subqueries.


UPDATE Publisher SET pubnum = NULL WHERE 0 =

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

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

In releases prior to V2R2.0, 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 not books in the library two rows of publisher are modified.

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

UPDATE Pubolisher SET PubNum = NULL WHERE 0 =

(SELECT C0UNT(*) 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. The section Correlated Subqueries, in Chapter 9, describes rules for specifying statements, including subqueries that will not provide different responses in this and future releases than were provided by prior releases.

The following examples illustrate the use of UPDATE:

The following statement may be used to update the YrsExp column in the Employee table for Greene, who has an EmpNo of 10017:

UPDATE Employee SET YrsExp = 16 WHERE EmpNo = 10017;


Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide


The following statement can be used to update the Employee table Example 2 to apply a 10 percent cost of living increase for all employees:

UPDATE Employee SET Salary = Salary * 1.1 ALL ;

The following statement may be used to place a null in the salary Example 3 column for the employee named Peterson:

UPDATE EMployee SET Salary = NULL WHERE EmpNo = 10001

Example 4

UPDATE t1 SET x1=1 WHERE x1 IN (SELECT x2 FROM T2); UPDATE t1 SET x1=1 WHERE t1.x=t2.x;

Teradata RDBMS for UNIX SQL Reference

Teradata SQL Syntax Guide

USING Modifier



USING Modifier

The USING modifier defines one or more variable parameter names. A value is substituted for each parameter name when the modified request is processed. Each value is retrieved as a data record from a client system-resident source such as a disk file. The data records are passed to the Teradata RDBMS as message parcels along with the text of the request.
Previous << 1 .. 178 179 180 181 182 183 < 184 > 185 186 187 188 189 190 .. 241 >> Next