SQL/MX Programming Manual for C and COBOL (G06.24+, H06.03+)

Static Rowsets
HP NonStop SQL/MX Programming Manual for C and COBOL—523627-004
7-39
Deleting Rows by Using Rowset-Derived Tables
Deleting Rows by Using Rowset-Derived Tables
Use a rowset-derived table in a DELETE statement to indicate which rows are to be
deleted from the database table. In this case, the values of the rowset are generated
from a subquery placed in the WHERE clause of the DELETE statement.
Example
This example deletes all rows from the JOB table specified by the hva_jobcode host
variable array:
EXEC SQL BEGIN DECLARE SECTION;
char SQLSTATE[6];
ROWSET[5] unsigned NUMERIC (4) hva_jobcode;
...
EXEC SQL END DECLARE SECTION;
...
/* Populate the rowset in some way. */
hva_jobcode[0] = 100;
hva_jobcode[1] = 200;
hva_jobcode[2] = 300;
hva_jobcode[3] = 400;
hva_jobcode[4] = 500;
...
EXEC SQL
DELETE FROM persnl.job
WHERE jobcode IN
(SELECT jobcode FROM ROWSET(:hva_jobcode) AS rs(jobcode));
...
The number of deleted rows is stored in the ROW_COUNT field of the statement
information in the diagnostics area. You can retrieve the value in the ROW_COUNT
field by using the GET DIAGNOSTICS statement.
...
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 sqlstate pic x(5).
01 rs.
02 ROWSET[5] hvajobcode pic 9(4) comp.
EXEC SQL END DECLARE SECTION END-EXEC.
...
***** Populate the rowset in some way ****
Move 100 TO hvajobcode(1)
Move 200 TO hvajobcode(2)
Move 300 TO hvajobcode(3)
Move 400 TO hvajobcode(4)
Move 500 TO hvajobcode(5)
EXEC SQL
DELETE FROM job
WHERE jobcode IN
(SELECT jobcode FROM ROWSET(:hvajobcode)
AS rs(jobcode))
END-EXEC.
...
C
COBOL