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 .. 160 161 162 163 164 165 < 166 > 167 168 169 170 171 172 .. 241 >> Next


INSERT INTO Employee (Name/EmpNo/DeptNo/DOB/SeX/EdLev) VALUES ('SMITH T'/10021/700/460729/'F'/16);

This example uses a SELECT subquery to insert a row for every Example 2 employee who has more than ten years of experience into a new

table, Promotion, which contains three columns defined as Name, DeptNo, and YrsExp.

INSERT INTO Promotion (DeptNum/ EmpName/ YearsExp) SELECT DeptNo/ Name/ YrsExp FROM Employee WHERE YrsExp > 10 ;

The INSERT operation performed in the previous example may also Example 3 be accomplished by the following statement. Note that a column

name list is not given for the Promotion table; therefore, the Employee columns referenced in the SELECT subquery must match, in both quantity and sequence, the columns contained by Promotion.

INSERT INTO Promotion

SELECT Name/ DeptNo/ YrsExp FROM Employee WHERE YrsExp > 10 ;

To add a row to the Employee table for new employee Orebo, you Example 4 could enter the following statement:

INSERT INTO employee

(name/ empno/ deptno/ dob/ seX/ edlev)

VALUES

('Orebo B'/ 10005/ 300/ 'Nov 17 1957'/ 'M'/ 18) ;

In this example, you list the columns that are to receive the data, followed by the data to be inserted, in the same order as the columns are listed. If you don’t specify a field value for a column named, a null value is stored.

Teradata RDBMS for UNIX SQL Reference

8-197
Teradata SQL Syntax Guide

INSERT

You could achieve the same result using the statement:

Example 5

´ INSERT INTO employee

(10005, 'Orebo B',300,,,, 'Nov 17 1957', 'M',,,18,);

With this example, you don’t specify column names for fields because you enter the field data in the same order as the columns are defined in the Employee table. In all cases, a comma is required as a place marker for any field for which data is not specified.

For ANSI compliance, the keyword NULL should be used for nulls, for example, (10005, ‘Ore60B’, 300, NULL, NULL, NULL, ‘Nov 17 1957’, ‘M’, NULL, NULL, 18)

8-198

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

LOCKING Modifier

LOCKING Modifier

The LOCKING modifier is used to lock a database, table, view, or Function row.

The LOCKING modifier overrides the default usage lock that the Teradata RDBMS places on a database, table, view, or row in response to a request.

The LOCKING modifier is flagged as non-ANSI, when the SQL flagger is enabled.

Syntax

- statement-

UT

where:

FF07B031

Syntax Element ... Specifies . . .
DATABASE TABLE VIEW optional keywords that introduce the name of a database, table, or view that is to be locked
ROW an optional keyword specifying a row or rows to be locked in accordance with the defining statement (see below).
dbname the name of a database (or user) that is to be locked.
tname the name of the table to be locked.
vname the name of the view to be locked.
FOR IN an introduction to the type of lock to be placed.

Teradata RDBMS for UNIX SQL Reference

8-199
Teradata SQL Syntax Guide

LOCKING Modifier

Syntax Element ... Specifies . . .
ACCESS READ SHARE WRITE EXCLUSIVE the type of lock to be placed. The SHARE keyword may be used as a synonym for READ. The CHECKSUM option is only available with Updatable Cursors through the Preprocessor. Refer to the Teradata Application Programming With Embedded SQL for C, Cobol, and PL/I.
MODE an optional keyword that may be used for SQL compatibility.
NOWAIT Indicates that if the indicated lock cannot be obtained, the statement should be aborted. Used for situations where it is not desirable to have a statement wait for resources, possibly also tying up resources in the process of waiting.
statement Specifies a SQL statement. If the SQL statement is null, the only effect of LOCKING is to lock the specified object. Statement is optional for database, table, and view. Statement is mandatory for row.

The Teradata RDBMS automatically places usage locks each time a SQL statement is entered. Therefore, it is never necessary to use the LOCKING modifier. The LOCKING modifier is only needed to override automatic locking when another lock setting is desired.

When a lock other than the default lock is needed, the LOCKING modifier should precede the SQL statement that is to be affected by the lock. When the LOCKING modifier is executed, the Teradata RDBMS places the specified lock on the object referenced by the modifier for the duration of the transaction in which the SQL statement resides. If the transaction is a single-statement transaction, the lock is only in effect during the processing of that statement.

The Locking modifier can be used to specify a database, table, or row lock.

_ The use of LOCK ROW prevents possible deadlocks occurring

Using LOCK ROW when two simultaneous primary key SELECTs are followed by an

UPDATE, DELETE, or INSERT on the same row. For example:

User A:

BEGIN TRANSACTION;

SELECT y FROM t WHERE x = 1;

UPDATE t SET y=0 WHERE x = 1;
Previous << 1 .. 160 161 162 163 164 165 < 166 > 167 168 169 170 171 172 .. 241 >> Next