HP NonStop SQL/MP User Manual

Data Retrieval and Modification
HP NonStop SQL/MP Programming Manual for C—429847-008
4-23
Multirow DELETE Statement
...
EXEC SQL OPEN get_by_partnum;
... /* Set values of the host variables. */
EXEC SQL FETCH get_by_partnum INTO ... ;
... /* Test the value(s) in the current row. */
/* Update the current row */
EXEC SQL UPDATE sales.parts
SET parts.partdesc = :new_partdesc,
parts.price = :new_price,
parts.qty_available = :new_qty
WHERE CURRENT OF get_by_partnum;
... /* Branch back to FETCH to get the next row. */
EXEC SQL CLOSE get_by_partnum;
Multirow DELETE Statement
When used with a cursor, a DELETE statement deletes multiple rows one row at a time
from a table or protection view. You identify the set of rows to delete (or test) in the
associated SELECT statement. Before you delete a row, you can test one or more
column values, and then if you decide to delete the row, specify the WHERE
CURRENT OF clause in the DELETE statement.
If you delete all rows from a table, the table still exists until it is deleted from the catalog
by a DROP TABLE statement.
To run a DELETE statement, a process started by the program must have read and
write access to the table or view and to tables or views specified in subqueries of the
search condition. For more information about process access, see Required
Access Authority on page 7-1.
A DELETE statement must run within the scope of all other SQL statements, including
the DECLARE CURSOR, OPEN, FETCH, INSERT, and CLOSE statements, that refer
to the cursor. For audited tables and views, the DELETE statement must run within the
same TMF transaction as the OPEN and FETCH statements for the cursor.
This example declares a cursor get_by_partnum, fetches data from the PARTS
table, tests the data, and then deletes specific rows:
EXEC SQL DECLARE get_by_partnum CURSOR FOR
SELECT partnum,
partdesc,
price,
qty_available
Note. Do not use a stand-alone DELETE statement to delete a row that has been retrieved
using a FETCH statement. A stand-alone DELETE statement can invalidate the cursor’s
buffering for the table and degrade performance.

HP NonStop SQL/MP Specifications

General

Brand	HP
Model	NonStop SQL/MP
Category	Software
Language	English