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

Simple and Compound Statements
HP NonStop SQL/MX Programming Manual for C and COBOL—523627-004
5-12
Searched DELETE Statement
WHERE jobcode = :SET-TO-NULL
END-EXEC.
Searched DELETE Statement
The searched DELETE statement deletes one or more rows from a table. The
selection of the rows to be deleted is based on a search condition. If you delete all
rows from a table, the table still exists until it is deleted by a DROP TABLE statement.
Use this general syntax:
For complete syntax, see the DELETE statement in the SQL/MX Reference Manual.
To delete a set of rows one row at a time by using a cursor, see Section 6, Static SQL
Cursors.
To execute a DELETE statement, a program must have DELETE privileges on the
table.
After a DELETE statement executes, NonStop SQL/MX returns a value to SQLSTATE.
If no rows were found satisfying the search condition, the value of SQLSTATE is
02000 (no data). For information on SQLSTATE values, see Table 13-1 on page 13-2.
Deleting a Single Row
To delete a single row, move a unique key value to a host variable and then specify the
host variable in the WHERE clause.
Example
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 hv_empnum host
variable:
EXEC SQL
DELETE FROM persnl.employee
WHERE empnum = :hv_empnum;
Deleting Multiple Rows
If you do not need to check a column value before you delete a row, you can use a
single DELETE statement to delete multiple rows in a table.
DELETE FROM table-name
WHERE search-condition
C