SQL/MP Programming Manual for COBOL

Data Retrieval and Modification
HP NonStop SQL/MP Programming Manual for COBOL—529758-003
4-10
Updating Columns With Null Values
than HOSTVAR-MIN-SALARY. A user enters the values for HOSTVAR-INC and
HOSTVAR-MIN-SALARY.
EXEC SQL UPDATE PERSNL.EMPLOYEE
SET SALARY = SALARY * :HOSTVAR-INC
WHERE SALARY < :HOSTVAR-MIN-SALARY END-EXEC.
This example updates all rows in the EMPLOYEE. DEPTNUM column that contain the
value in HOSTVAR-OLD-DEPTNUM. After the update, all employees who were in the
department specified by HOSTVAR-OLD-DEPTNUM are moved to the department
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-12.)
To execute a DELETE statement, a process started by the program must have read
and write access to the table or view that contains the rows to be deleted and to tables