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

SQL/MX Statements

HP NonStop SQL/MX Reference Manual—523725-004

2-118

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;