SQL/MP Programming Manual for COBOL

Data Retrieval and Modification
HP NonStop SQL/MP Programming Manual for COBOL—529758-003
4-11
Deleting a Single Row
or views in subqueries of the search condition. For information about process access,
see Required Access Authority on page 7-1.
SQL/MP returns these values to SQLCODE after a DELETE statement.
After a successful DELETE operation, the SQLCA structure contains the number of
rows deleted. If an error occurs on a DELETE operation, the SQLCA contains the
approximate number of rows deleted. To return the contents of the SQLCA, use
SQLCA_DISPLAY2_ or SQLCA_TOBUFFER2_ procedure.
For more information, see Section 5, SQL/MP System Procedures, and Section 9,
Error and Status Reporting.
Deleting a Single Row
To delete a single row, move a key value to a host variable and then specify the host
variable in the WHERE clause. This DELETE statement deletes only one row of the
EMPLOYEE table because each value in EMPNUM (the primary key) is unique. A user
enters the value for the host variable named HOSTVAR-EMPNUM.
EXEC SQL DELETE FROM PERSNL.EMPLOYEE
WHERE EMPNUM = :HOSTVAR-EMPNUM END-EXEC.
Deleting Multiple Rows
If you do not need to check a column value before you delete a row, use a single
DELETE statement to delete multiple rows in a table. This example deletes all rows (or
employees) from the EMPLOYEE table specified by DELETE-DEPTNUM (which is
entered by a user).
EXEC SQL DELETE FROM PERSNL.EMPLOYEE
WHERE DEPTNUM = :DELETE-DEPTNUM END-EXEC.
This example deletes all suppliers from the PARTSUPP table who charge more than
TERMINAL-MAX-COST for a terminal. Terminal part numbers range from TERMINAL-
FIRST-NUM to TERMINAL-LAST-NUM.
EXEC SQL DELETE FROM INVENT.PARTSUPP
WHERE PARTNUM BETWEEN :TERMINAL-FIRST-NUM
AND :TERMINAL-LAST-NUM
AND PARTCOST > :TERMINAL-MAX-COST END-EXEC.
SQLCODE Value Description
0
The DELETE statement was successful.
100
No rows were found on a search condition.
< 0
An error occurred; SQLCODE contains the error number.
> 0 (not 100)
A warning occurred; SQLCODE contains the first warning number.