HP NonStop SQL/MP, Programming Manual

Page: 81 / 331

Data Retrieval and Modification
HP NonStop SQL/MP Programming Manual for C—429847-008
4-13
Deleting a Single Row
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 details, see
Required Access Authority on page 7-1.
NonStop 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, 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 example 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
hostvar_empnum
.
EXEC SQL DELETE FROM persnl.employee
WHERE empnum = :hostvar_empnum;
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 ;
This example deletes all suppliers from the PARTSUPP table who charge more than
terminal_max_cost
for a terminal. Terminal part numbers range from
term_first_num
to
term_last_num
.
EXEC SQL DELETE FROM invent.partsupp
WHERE partnum BETWEEN :term_first_num AND :term_last_num
AND partcost > :terminal_max_cost ;
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 (¦100) A warning occurred;
sqlcode
contains the first warning number.