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

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—523725-004
2-116
C Examples of DELETE
--- 1 row(s) deleted.
•
Remove from the table ORDERS any orders placed with sales representative 220
by any customer except customer number 1234:
DELETE FROM sales.orders
WHERE salesrep = 220 AND custnum <> 1234;
--- 2 row(s) deleted.
•
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