MP Users

DML Features

HP NonStop SQL/MX Comparison Guide for SQL/MP Users—523735-003

3-16

UPDATE Statement

Source Value for a Column Referenced in the SET Clause

In NonStop SQL/MX, the source value for a column referenced in the SET clause of an

UPDATE statement is an SQL value expression. In particular, a value expression can

be a scalar subquery:

UPDATE JOB SET jobdesc=(SELECT jobdesc from JOB1

WHERE jobcode=2000)

WHERE jobcode=2000;

In NonStop SQL/MP, the expression cannot be a subquery.

Parallel Plans

In NonStop SQL/MP, you cannot have a parallel plan where the UPDATE statement

contains the WHERE CURRENT OF clause. In NonStop SQL/MX, you can have

parallel plans with the WHERE CURRENT OF clause.

Access Options and Isolation Levels

NonStop SQL/MP allows you to specify access options for each DML statement. In

NonStop SQL/MP, the statement level access options are BROWSE, STABLE, and

REPEATABLE.

NonStop SQL/MX allows you to specify the isolation level at both the statement and

transaction level. (Isolation level, SQL:1999 terminology, means the same as access

option.) In NonStop SQL/MX, the isolation levels for both statements and transactions

are READ UNCOMMITTED, READ COMMITTED, SERIALIZABLE (or REPEATABLE

READ) and STABLE. Isolation levels set at the statement level take precedence over

the transaction level settings. Isolation levels for statements are extensions to the ANSI

standard. Isolation levels for transactions are SQL:1999 compliant; you set transaction

isolation levels with the SET TRANSACTION statement.

Isolation Levels Contrasted Between NonStop SQL/MP and

NonStop SQL/MX

•

BROWSE (NonStop SQL/MP) and READ UNCOMMITTED (NonStop SQL/MX)

have the same implementation. This option is available only for the SELECT

statement.

Note. Unless you are writing applications for portability, you should use isolation levels at the

statement level. The SET TRANSACTION statement might cause a dynamic recompilation of

DML statements within the next transaction. Further, NonStop SQL/MX does not have the

opportunity to optimize query execution plans as it does during static compilation. You can

achieve better performance if you specify isolation levels for individual DML statements.