SQL/MX 3.x Reference Manual (H06.22+, J06.11+)

SQL/MX Statements

HP NonStop SQL/MX Reference Manual—640322-001

2-124

Publish/Subscribe Examples of DELETE

Suppose that these SQL/MP tables and index (and the metadata mappings) have been

created:

CREATE TABLE $db.dbtab.tab1 (a INT NOT NULL, b INT, c INT);

CREATE TABLE $db.dbtab.tab2 (a INT, b INT, c INT);

CREATE INDEX $db.dbtab.itab1 ON tab1(b, c);

CREATE SQLMP ALIAS cat.sch.tab1 $db.dbtab.tab1;

CREATE SQLMP ALIAS cat.sch.tab2 $db.dbtab.tab2;

•

This example shows the SET ON ROLLBACK clause. The SET ON ROLLBACK

column must be declared as NOT NULL; it cannot be part of a secondary index.

SET NAMETYPE ANSI;

SET SCHEMA cat.sch;

DELETE FROM tab1

SET ON ROLLBACK a = a + 1;

•

This example shows the SET ON ROLLBACK clause in an embedded delete of a

SELECT statement:

SELECT * FROM

(DELETE FROM tab1 SET ON ROLLBACK a = a + 1) tab1;

•

This example shows SKIP CONFLICT ACCESS used with an embedded delete

statement accessing a table as a stream:

SELECT a FROM (DELETE FROM STREAM(tab1)

WHERE a = 1 FOR SKIP CONFLICT ACCESS) as tab1;