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 .. 184 185 186 187 188 189 < 190 > 191 192 193 194 195 196 .. 241 >> Next


(SELECT MAX(salary) FROM personnel sp WHERE p.department=sp.department);

The above query returns the name of employees who have the highest salary in each department.

IThis query references no tables so no FROM clause is needed.

9-12

Teradata RDBMS for UNIX SQL Reference
Advanced SQL

Correlated Subqueries and UPDATE

Correlated Subqueries and UPDATE

In this release, a FROM clause has been added to the UPDATE statement.

If a FROM clause is required, any alias name must be specified in the FROM clause. If an alias is specified for the updated table name in the FROM clause, this alias name, instead of the original name, must be used as ‘tablename’ after the UPDATE keyword. This FROM clause is optional if no joined tables are specified for an UPDATE.

If the FROM clause is omitted, an alias name can be specified for tablename after the UPDATE keyword.

In this release, in both UPDATE and DELETE statements, the correlated subquery are provided in the search condition of the WHERE clause.

UPDATE-Ttname1 UPD-

Las-I

lFROMx- tname -

Las-I

®-

SET -

- col_name = expr -

------ALL-------

'- WHERE cond —1

FF07A040

aname

The following queries are:

Examples 0 . . ,

• Syntactically correct in all releases:

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

UPDATE t1 a SET x1=1 WHERE a.x2=2;

• Illegal in all releases prior to V2R2.0 but legal in the current and future releases:

UPDATE t1 FROM t1 SET x1=1 WHERE t1.x1=2;

• Syntactically correct in all releases prior to V2R2.0 but incorrect in release V2R2.0 and future releases:

UPDATE t1 SET x1=1 WHERE t1.x=t2.x;

To work correctly in the current release, the above query should be written as:

UPDATE t1 FROM t1, t2 SET x1=1 WHERE t1.x = t2.x ;

• Invalid in all releases:

UPDATE t1 FROM t2 SET x1=1 WHERE t1.x1=t2.x2;

Teradata RDBMS for UNIX SQL Reference

9-13
Advanced SQL

Correlated Subqueries and DELETE

Examples

Correlated Subqueries and DELETE

DELETE syntax has changed to require that if joined tables are specified for a delete, all tables referenced in the DELETE must be specified in the FROM clause. This includes the deleted table.

A ‘tablename’ will be added to specify the deleted table name in this case. All alias names must be defined in the FROM clause. If an alias is defined for the deleted table name in the FROM clause, this alias name, and not the original name, must be used as tablename after the DELETE keyword. The tablename preceding the FROM clause is optional if no joined tables are specified for a DELETE.

The DELETE syntax is as follows:

• With implied join condition:

DELETE----del tname----FROM----------------------------

A

- tname -

EAST

e

—WHERE— condition — L;J '
ALL

FF07A073

• Without implied join (“basic form”):

DELETET--------tname

FROM

aname -I -WHERE - condition -
UsJ
ALL

FF07B072

The following queries are:

• Syntactically correct in all releases:

DELETE FROM t1 WHERE x1 IN (SELECT x2 FROM T2);

DELETE FROM t1 a WHERE a.x1=2;

• Illegal in releases prior to V2R2.0 but legal in the current and future releases:

DELETE t1 FROM t1 WHERE t1.x1=2;

DELETE t1 FROM t1, t2 WHERE t1.x1=t2.x2;

DELETE a FROM t1 a, t2 WHERE a.x1=t2.x2;

9-14

Teradata RDBMS for UNIX SQL Reference
Advanced SQL

Correlated Subqueries and DELETE

• Syntactically correct in releases prior to V2R2.0 but incorrect in the current release.

DELETE FROM t1 WHERE t1.x1=t2.x2;

To work correctly in the current release, rewrite this query as:

DELETE t1 FROM t1,t2 WHERE t1.x1 = t2.x2 ;

• Invalid in all releases:

DELETE t1 FROM t1, t2 WHERE t1.x1=t2.x2 AND t1.n=t3.n;

DELETE t1 FROM t2 WHERE t1.x1=t2.x2;

DELETE t1 FROM t1 a, t2 WHERE a.x1=t2.x2;

DELETE t1 a FROM t1, t2 WHERE a.x1=t2.x2;

Teradata RDBMS for UNIX SQL Reference

9-15
Advanced SQL

Correlated Subqueries and ABORT/ROLLBACK

Examples

Correlated Subqueries and ABORT/ROLLBACK

In the current release, a FROM clause has been added and is required for an ABORT statement if the ABORT references a table. All tables referenced in an ABORT statement must be defined in a FROM clause.

The ABORT and ROLLBACK statements are now supported as extensions to ANSI syntax. A WHERE clause is supported as part of these statements. Correlated subqueries and the EXISTS predicate are supported in this WHERE clause.

ABORT- ~ '

LquotestringLFROM optionLWHERE clause

FF07A068

The following queries are:

• Syntactically correct in all releases:

ABORT WHERE user='DBC'

• Illegal in releases prior to V2R2.0 but legal in the current and future releases:

ABORT FROM t1 WHERE t1.x1 > 1;

• Invalid in all releases.

ABORT FROM t1 WHERE t1.x1=t2.x2;

9-16

Teradata RDBMS for UNIX SQL Reference
Advanced SQL

SELECT COUNT(*)

SELECT COUNT(*)

To select the names of the publishers whose book count values in the inventory match the actual count of books, the following query can be used in releases prior to V2R2.0:
Previous << 1 .. 184 185 186 187 188 189 < 190 > 191 192 193 194 195 196 .. 241 >> Next