SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-115
C Examples of DELETE
•
Remove from the table PARTSUPP all suppliers who charge more than $1,600.00
for items that have part numbers in the range 6400 to 6700:
DELETE FROM invent.partsupp
WHERE partnum BETWEEN 6400 AND 6700
AND partcost > 300.00 SERIALIZABLE ACCESS;
--- 3 row(s) deleted.
This DELETE uses SERIALIZABLE access, which provides maximum consistency
but reduces concurrency. Therefore, you should run this statement at a time when
few users need concurrent access to the database.
•
Remove all suppliers not in Texas from the table PARTSUPP:
DELETE FROM invent.partsupp
WHERE suppnum IN
(SELECT suppnum FROM samdbcat.invent.supplier
WHERE state <> 'TEXAS');
--- 41 row(s) deleted.
This statement achieves the same result:
DELETE FROM invent.partsupp
WHERE suppnum NOT IN
(SELECT suppnum FROM samdbcat.invent.supplier
WHERE state = 'TEXAS');
--- 41 row(s) deleted.
C Examples of DELETE
•
Remove the row for JOHN WALKER from the EMPLOYEE table:
EXEC SQL DELETE FROM PERSNL.EMPLOYEE
WHERE FIRST_NAME = 'JOHN' AND LAST_NAME = 'WALKER'
SERIALIZABLE ACCESS;
•
Use a cursor and delete some of the returned rows during processing:
...
EXEC SQL DECLARE emp_cursor CURSOR FOR
SELECT EMPNUM, DEPTNUM, JOBCODE, SALARY
FOR UPDATE ACCESS
FROM PERSNL.EMPLOYEE
SERIALIZABLE ACCESS;
...
EXEC SQL OPEN emp_cursor;
...
EXEC SQL FETCH emp_cursor
INTO :hv_empnum, :hv_deptnum, :hv_jobcode, :hv_salary;
... /* Process fetched row. */
if (hv_jobcode == 1234)
EXEC SQL DELETE FROM PERSNL.EMPLOYEE
WHERE CURRENT OF emp_cursor;