SQL/MP Programming Manual for COBOL85

Data Retrieval and Modification
HP NonStop SQL/MP Programming Manual for COBOL85—429326-004
4-10
Updating Columns With Null Values
specified by HOSTVAR-NEW-DEPTNUM. A user enters the values for HOSTVAR-
OLD-DEPTNUM and HOSTVAR-NEW-DEPTNUM.
EXEC SQL UPDATE PERSNL.EMPLOYEE
SET DEPTNUM = :HOSTVAR-NEW-DEPTNUM
WHERE DEPTNUM = :HOSTVAR-OLD-DEPTNUM END-EXEC.
Updating Columns With Null Values
This example updates the specified SALARY column in the EMPLOYEE table to a null
value using an indicator variable. The SET-TO-NULLS host variable specifies the row
to update.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
EXEC SQL INVOKE PERSNL.EMPLOYEE AS EMP-TBL END-EXEC.
...
01 IND-1 PIC S9(4) COMP.
EXEC SQL END DECLARE SECTION END-EXEC.
...
PROCEDURE DIVISION.
...
MOVE -1 TO IND-1.
MOVE NULL-JOBCODE TO SET-TO-NULLS.
EXEC SQL UPDATE PERSNL.EMPLOYEE
SET SALARY =:SALARY OF EMP-TBL INDICATOR :IND-1
WHERE JOBCODE OF EMP-TBL = :SET-TO-NULLS END-EXEC.
This example uses the NULL keyword instead of an indicator variable:
EXEC SQL UPDATE PERSNL.EMPLOYEE
SET SALARY = NULL
WHERE JOBCODE = :SET-TO-NULLS END-EXEC.
DELETE Statement
The DELETE statement deletes one or more rows from a table or protection view. 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 delete a set of rows one row at a time by using a
cursor, see Using SQL Cursors on page 4-11.)
To execute a DELETE statement, a program’s PAID must have read and write access
to the table or view that contains the rows to be deleted and to tables or views in
subqueries of the search condition.
SQL/MP returns these values to SQLCODE after a DELETE statement.
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.