SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—523725-004
2-242
C Examples of UPDATE
The subquery is evaluated for each row of the DEPT table and returns department
numbers for departments located in Chicago.
•
Suppose that you want to change the employee number of a manager of a
department. Because EMPNUM is a primary key of the EMPLOYEE table, you
must delete the employee's record and insert a record with the new number.
You must also update the DEPT table to change the MANAGER column to the
employee's new number. To ensure all your changes take place (or that none of
them do), perform the operation as a transaction:
SET TRANSACTION
ISOLATION LEVEL SERIALIZABLE;
--- SQL operation complete.
BEGIN WORK;
--- SQL operation complete.
DELETE FROM persnl.employee
WHERE empnum = 23;
--- 1 row(s) deleted.
INSERT INTO persnl.employee
(empnum, first_name, last_name, deptnum, salary)
VALUES (50, 'JERRY','HOWARD', 1000, 137000.00);
--- 1 row(s) inserted.
UPDATE persnl.dept
SET manager = 50
WHERE deptnum = 1000;
--- 1 row(s) updated.
COMMIT WORK;
--- SQL operation complete.
This transaction uses SERIALIZABLE access, which provides maximum data
consistency.
C Examples of UPDATE
•
Reset the credit rating to the default value for all the customers in the CUSTOMER
table:
EXEC SQL UPDATE CUSTOMER SET CREDIT = DEFAULT;
•
Use a loop to fetch and update by using a cursor:
...
char SQLSTATE_OK[6]="00000"; /* variable declarations */
EXEC SQL BEGIN DECLARE SECTION;
char SQLSTATE[6];
...
EXEC SQL END DECLARE SECTION;
...
EXEC SQL FETCH cursor1 INTO SQL DESCRIPTOR 'out_sqlda';