MX Database and Application Migration Guide

Converting SQL/MP Applications to SQL/MX

Applications

HP NonStop SQL/MP to NonStop SQL/MX Database and Application Migration Guide—666211-001

4-22

INSERT Statements

RETURNING LASTSYSKEY Clause

NonStop SQL/MP supports the RETURNING LASTSYSKEY clause for the INSERT

statement. NonStop SQL/MX does not support this option.

Insert Rows in Order

NonStop SQL/MX allows you to insert rows in order with an ORDER BY clause on the

INSERT statement. When you select from one table to be inserted into another, an

ORDER BY clause can reorder the sequence of the insertion so that the inserts are

done more efficiently. You cannot use ORDER BY in NonStop SQL/MX when the

SELECT statement is a subquery. You cannot use the ORDER BY clause in embedded

SQL programs. The ORDER BY clause is a SQL/MX extension to the ANSI-92

standard. For additional information about the ORDER BY clause, see the

SQL/MX

Reference Manual

Inserting Multiple Rows

In NonStop SQL/MP, if you use the VALUES keyword, you can insert only one row. For

example, this SQL/MP statement inserts a single row of values:

INSERT INTO JOB VALUES (1000, 'MANAGER');

In NonStop SQL/MX, if you use the VALUES keyword, you can insert multiple row-

value constructors. For example, this SQL/MX statement inserts two rows into table

JOB1:

INSERT INTO JOB1 VALUES (1000, 'MANAGER'), (2000, 'SUPERVISOR');

Table 4-6. SQL/MX and SQL/MP DML Statements

SQL/MP DML

Statements

Implemented

in SQL/MX? Comments

DELETE Yes No changes are required for NonStop SQL/MX.

INSERT Changed In NonStop SQL/MX, you can use an ORDER

BY clause after you specify the insert source.

Also, you can use multiple row-value

constructors after the VALUES keyword. See

INSERT Statements

on page 4-22.

SELECT Changed In NonStop SQL/MX, you can use a SQL value

expression in a select list. Also, you can use

derived tables (or query expressions) in a FROM

clause. See SELECT Statements

on page 4-23.

UPDATE Changed In NonStop SQL/MX, you can use a scalar

subquery as the update value in a SET clause.

See UPDATE Statements

on page 4-24.