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 .. 177 178 179 180 181 182 < 183 > 184 185 186 187 188 189 .. 241 >> Next

tname the name of a table, view or macro. If database of omitted, it is inferred from context.
AS an optional keyword introducing the aname.
aname a different, temporary name (alias) for the table that is referenced by tname. aname must be used during a self-join operation on the table.
SET the names of one or more columns whose data is to be updated, and the expressions that are used for update.
col_name the column name. The col_name field is for the columnname only; forms such as Databasename.Tablename.Columnname, or Tablename.Columnname should not be used.

8-254

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

UPDATE

Syntax Element ... Specifies . . .
expr a new value to be placed into the column to be updated. expr may include a constant, a null value (expressed by the reserved word NULL), or an arithmetic expression for calculating the new value. Values in a targeted row before the update may be referenced in an expression.
WHERE a conditional clause. For more information see Chapter 7, “Queries: The SELECT Statement,” “WHERE Clause”. If WHERE is used, the user must have SELECT access on searched objects.
cond the conditional expression to be used for determining rows whose values are to be updated. The conditional clause may reference more than one table, or specify an embedded select operation.
ALL Indicates that all rows in the specified table are to be updated. The ALL option is a Teradata RDBMS extension to ANSI SQL.

To update a table, a user must have the UPDATE privilege on the

Privileges Required table or columns to be updated.

When executing UPDATE and it requires READ access to an object, the user must have the SELECT right to data being accessed. For example:

UPDATE t1 SET f1=1 WHERE f1<0;

Here, READ access is required by the condition.

UPDATE t1 SET f1=f1+1;

Here, READ access is required in order to compute f1 + 1 from old values.

An UPDATE operation sets a write lock for the table or row.

The activity count in the success response for an UPDATE statement reflects the total number of rows updated. If no rows qualify for update, the activity count is zero.

Be aware that, unless a table is created as MULTISET (without Duplicat6 Row Checks UNIQUE constraints), to allow duplicate rows, the Teradata RDBMS

checks for duplicate rows during the update process, the order in which updates are executed may affect the result of a transaction. Consider the following example:

CREATE SET TABLE T1 (A INTEGER, B INTEGER) PRIMARY INDEX (A); INSERT INTO T1 VALUES (1,1);

INSERT INTO T1 VALUES (1,2);

UPDATE T1 SET B = B + 1 WHERE A = 1; /* fails */

UPDATE T1 SET B = B - 1 WHERE A = 1; /* succeeds */

Teradata RDBMS for UNIX SQL Reference

8-255
Teradata SQL Syntax Guide

UPDATE

UPDATE Processing Time

Updating Rows Using Views

Non-Valid Uses of UPDATE

The first UPDATE statement fails because it creates a duplicate row. If the order of the UPDATE statements is reversed, then both UPDATE statements would succeed.

This mode is characteristic of simple updates only; join updates, or updates that affect primary or secondary index values are implemented as discrete delete and insert operations.

Processing time for an UPDATE operation is longer:

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

• When a column on which one or more indexes (secondary or primary) are defined is updated.

Processing time for an UPDATE operation may be shortened by specifying a primary or secondary index key in the WHERE clause of the UPDATE statement.

To update rows using a view through which the table is accessed, the following rules must be observed:

• The user must have the UPDATE privilege on the view. Also, the immediate owner of the view (that is, the database in which the view resides) must have the UPDATE privilege on the underlying object (view, base table, or columns) whose columns are to updated, 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.

• If the statement used to define a view contains a WHERE clause, and WITH CHECK OPTION, all values inserted through that view must satisfy constraints specified in the WHERE clause.

If a view includes a WHERE clause and does not include WITH CHECK OPTION, data may be inserted, which will not be visible through that view.

An UPDATE operation causes an error message to be returned when:

• The operation attempts to update a field using a value that violates a constraint (for example, UNIQUE or referential) declared for the column.

8-256

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

UPDATE

• The operation attempts to update a field using a value that is of a different numeric type than that declared for the column and the value cannot be converted correctly to the correct type.
Previous << 1 .. 177 178 179 180 181 182 < 183 > 184 185 186 187 188 189 .. 241 >> Next