SQL/MX 2.x Database and Application Migration Guide (G06.23+, H06.04+, J06.03+)

Converting SQL/MP Applications to SQL/MX
Applications
HP NonStop SQL/MX Database and Application Migration Guide—540435-005
10-22
INSERT Statements
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 selecting rows 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 an 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 10-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 10-22.
SELECT Changed In NonStop SQL/MX, you can use an 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 10-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 10-24.