| >> |
| >>create index t015t1i1 on t015t1(a); |
| |
| --- SQL operation complete. |
| >>create index t015t1i2 on t015t1(b); |
| |
| --- SQL operation complete. |
| >> |
| >>create index t015t2i1 on t015t2(a); |
| |
| --- SQL operation complete. |
| >>create index t015t2i2 on t015t2(b); |
| |
| --- SQL operation complete. |
| >> |
| >>create index t015t3i1 on t015t3(a); |
| |
| --- SQL operation complete. |
| >>create index t015t3i2 on t015t3(b); |
| |
| --- SQL operation complete. |
| >>create index t015t3i3 on t015t3(c); |
| |
| --- SQL operation complete. |
| >> |
| >>create index t015t4i1 on t015t4(a); |
| |
| --- SQL operation complete. |
| >>create index t015t4i2 on t015t4(b); |
| |
| --- SQL operation complete. |
| >>create index t015t4i3 on t015t4(c); |
| |
| --- SQL operation complete. |
| >> |
| >>create index t015t5i1 on t015t5(a); |
| |
| --- SQL operation complete. |
| >>create index t015t5i2 on t015t5(b); |
| |
| --- SQL operation complete. |
| >>create index t015t5i3 on t015t5(c); |
| |
| --- SQL operation complete. |
| >>create index t015t5i4 on t015t5(d); |
| |
| --- SQL operation complete. |
| >> |
| >>create index t015t6i1 on t015t6(a); |
| |
| --- SQL operation complete. |
| >>create index t015t6i2 on t015t6(b); |
| |
| --- SQL operation complete. |
| >>create index t015t6i3 on t015t6(c); |
| |
| --- SQL operation complete. |
| >>create index t015t6i4 on t015t6(d); |
| |
| --- SQL operation complete. |
| >> |
| >>create unique index t015t8i1 on t015t8(j); |
| |
| --- SQL operation complete. |
| >>create unique index t015t8i2 on t015t8(k); |
| |
| --- SQL operation complete. |
| >> |
| >>create unique index t015t10i1 on t015t10(j); |
| |
| --- SQL operation complete. |
| >>create unique index t015t10i2 on t015t10(k); |
| |
| --- SQL operation complete. |
| >> |
| >>create index t015t12i1 on t015t12(b) ; |
| |
| --- SQL operation complete. |
| >>create index t015t12i2 on t015t12(c) salt like table; |
| |
| --- SQL operation complete. |
| >> |
| >>create index t015t13i1 on t015t13(c) ; |
| |
| --- SQL operation complete. |
| >> |
| >>CREATE TABLE DEC1 |
| +>( |
| +>ID int unsigned GENERATED BY DEFAULT AS IDENTITY (CACHE 5000) NOT NULL, |
| +>codeValue varchar(1000), |
| +>title varchar(100), |
| +>shortName varchar(200), |
| +>description varchar(1000), |
| +>path varchar(1000), |
| +>synonyms varchar(1000), |
| +>objectClassCode varchar(100), |
| +>propertyCode varchar(100), |
| +>PRIMARY KEY (ID) |
| +>); |
| |
| --- SQL operation complete. |
| >>CREATE INDEX IDX3_DEC1_T_CodeValue ON DEC1(codeValue); |
| |
| --- SQL operation complete. |
| >> |
| >> |
| >>CREATE TABLE DE |
| +>( |
| +>ID int unsigned GENERATED BY DEFAULT AS IDENTITY (CACHE 5000) NOT NULL, |
| +>dataElementConceptID int unsigned, |
| +>valueDomainID int unsigned, |
| +>PRIMARY KEY (ID) |
| +>); |
| |
| --- SQL operation complete. |
| >> |
| >>CREATE TABLE ODT |
| +>( |
| +>ID int unsigned GENERATED BY DEFAULT AS IDENTITY (CACHE 5000) NOT NULL, |
| +>objectTypeID int unsigned NOT NULL, |
| +>dataElementID int unsigned NOT NULL, |
| +>objectCategoryTypeCode varchar(20) NOT NULL, |
| +>nounObjectDataTypeID int unsigned, |
| +>PRIMARY KEY (ID) |
| +>); |
| |
| --- SQL operation complete. |
| >> |
| >>ALTER TABLE DE |
| +>ADD CONSTRAINT FK1_DE_DEC1 |
| +>FOREIGN KEY (dataElementConceptID) |
| +>REFERENCES DEC1(ID); |
| |
| --- SQL operation complete. |
| >> |
| >>CREATE INDEX IDX1_DE ON DE(dataElementConceptID, valueDomainID); |
| |
| --- SQL operation complete. |
| >> |
| >>ALTER TABLE ODT |
| +>ADD CONSTRAINT FK2_ODT_DE |
| +>FOREIGN KEY (dataElementID) |
| +>REFERENCES DE(ID); |
| |
| --- SQL operation complete. |
| >> |
| >>CREATE INDEX IDX1_ODT ON ODT(objectTypeId,objectCategoryTypeCode, dataElementID); |
| |
| --- SQL operation complete. |
| >> |
| >>CREATE TABLE OT |
| +>( |
| +>ID int unsigned GENERATED BY DEFAULT AS IDENTITY (CACHE 1000) NOT NULL, |
| +>objectCode varchar(100), |
| +>version varchar(2), |
| +>PRIMARY KEY (ID) |
| +>); |
| |
| --- SQL operation complete. |
| >> |
| >>ALTER TABLE OT |
| +>ADD CONSTRAINT UI1_OT UNIQUE (objectCode, version); |
| |
| --- SQL operation complete. |
| >> |
| >>create table t015t16 (a largeint not null, b int not null, |
| +>c int, d int, e int, primary key(a,b)) ; |
| |
| --- SQL operation complete. |
| >>create index t015t16i1 on t015t16(c) ; |
| |
| --- SQL operation complete. |
| >>create unique index t015t16i2 on t015t16(d) ; |
| |
| --- SQL operation complete. |
| >> |
| >>prepare explainIt from |
| +>select substring(operator,1,16) operator |
| +>from table (explain(NULL,'XX')) t |
| +>where operator LIKE '%TRAFODION_MERGE%' ; |
| |
| --- SQL command prepared. |
| >> |
| >>prepare explainItEff from |
| +>select substring(operator,1,16) operator |
| +>from table (explain(NULL,'XX')) t |
| +>where operator LIKE '%SEQUENCE%' ; |
| |
| --- SQL command prepared. |
| >> |
| >> |
| >>prepare explainItVsbb from |
| +>select substring(operator,1,25) operator |
| +>from table (explain(NULL,'XX2')) t |
| +>where operator LIKE '%SEQUENCE%' OR operator LIKE '%VSBB_UPSERT%' OR operator LIKE '%VSBB_DELETE%' ; |
| |
| --- SQL command prepared. |
| >> |
| >> |
| >>insert into t015t1 values (1,1), (2,2); |
| |
| --- 2 row(s) inserted. |
| >>insert into t015t3 values (1,1,1), (2,2,2), (3,3,3); |
| |
| --- 3 row(s) inserted. |
| >>insert into t015t5 values (1,2,3,4,5); |
| |
| --- 1 row(s) inserted. |
| >>insert into t015t6 values (1,2,3,4,5,6,7); |
| |
| --- 1 row(s) inserted. |
| >> |
| >>-- multi column updates |
| >> |
| >>-- simple updates |
| >>update t015t3 set (b,c) = (4,4) where a = 2; |
| |
| --- 1 row(s) updated. |
| >>select * from t015t3; |
| |
| A B C |
| ----------- ----------- ----------- |
| |
| 1 1 1 |
| 2 4 4 |
| 3 3 3 |
| |
| --- 3 row(s) selected. |
| >>update t015t3 set (b,c) = (5,5); |
| |
| --- 3 row(s) updated. |
| >>select * from t015t3; |
| |
| A B C |
| ----------- ----------- ----------- |
| |
| 1 5 5 |
| 2 5 5 |
| 3 5 5 |
| |
| --- 3 row(s) selected. |
| >>update t015t3 set (b,c) = (b+1,c-1); |
| |
| --- 3 row(s) updated. |
| >>select * from t015t3; |
| |
| A B C |
| ----------- ----------- ----------- |
| |
| 1 6 4 |
| 2 6 4 |
| 3 6 4 |
| |
| --- 3 row(s) selected. |
| >>update t015t6 set b = 1, (c,d) = (7,7), (e,f,g) = (8,8,8); |
| |
| --- 1 row(s) updated. |
| >>select * from t015t6; |
| |
| A B C D E F G |
| ----------- ----------- ----------- ----------- ----------- ----------- ----------- |
| |
| 1 1 7 7 8 8 8 |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- updates using subquery |
| >>update t015t3 set (b,c) = (select a,b from t015t1 where a = 1) |
| +> where a = 3; |
| |
| --- 1 row(s) updated. |
| >>select * from t015t3; |
| |
| A B C |
| ----------- ----------- ----------- |
| |
| 1 6 4 |
| 2 6 4 |
| 3 1 1 |
| |
| --- 3 row(s) selected. |
| >>update t015t3 set (b,c) = (select a+1,10 from t015t1 where a = 1) |
| +> where a = 3; |
| |
| --- 1 row(s) updated. |
| >>select * from t015t3; |
| |
| A B C |
| ----------- ----------- ----------- |
| |
| 1 6 4 |
| 2 6 4 |
| 3 2 10 |
| |
| --- 3 row(s) selected. |
| >>update t015t3 set (b,c) = (select a+1,10 from t015t1 where a = 1); |
| |
| --- 3 row(s) updated. |
| >>select * from t015t3; |
| |
| A B C |
| ----------- ----------- ----------- |
| |
| 1 2 10 |
| 2 2 10 |
| 3 2 10 |
| |
| --- 3 row(s) selected. |
| >> |
| >>-- simple merge stmt |
| >>delete from t015t1; |
| |
| --- 2 row(s) deleted. |
| >>merge into t015t1 on a = 1 when matched then update set b = -1 |
| +> when not matched then insert values (1,2); |
| |
| --- 1 row(s) updated. |
| >>select * from t015t1; |
| |
| A B |
| ----------- ----------- |
| |
| 1 2 |
| |
| --- 1 row(s) selected. |
| >>merge into t015t1 on a = 1 when matched then update set b = -1 |
| +> when not matched then insert values (1,2); |
| |
| --- 1 row(s) updated. |
| >>select * from t015t1; |
| |
| A B |
| ----------- ----------- |
| |
| 1 -1 |
| |
| --- 1 row(s) selected. |
| >>merge into t015t1 on a = 1 when matched then update set b = 1 where b > 0 |
| +> when not matched then insert values (1,2); |
| |
| --- 0 row(s) updated. |
| >>select * from t015t1; |
| |
| A B |
| ----------- ----------- |
| |
| 1 -1 |
| |
| --- 1 row(s) selected. |
| >>merge into t015t1 on a = 1 when matched then update set b = 1 where b < 0 |
| +> when not matched then insert values (1,2); |
| |
| --- 1 row(s) updated. |
| >>select * from t015t1; |
| |
| A B |
| ----------- ----------- |
| |
| 1 1 |
| |
| --- 1 row(s) selected. |
| >> |
| >>delete from t015t1; |
| |
| --- 1 row(s) deleted. |
| >>insert into t015t1 values(1,1),(2,2); |
| |
| --- 2 row(s) inserted. |
| >>merge into t015t1 on a = 1 when matched then update set b = -b where b = 0 |
| +> when not matched then insert values (3,3); |
| |
| --- 0 row(s) updated. |
| >>select * from t015t1; |
| |
| A B |
| ----------- ----------- |
| |
| 1 1 |
| 2 2 |
| |
| --- 2 row(s) selected. |
| >>merge into t015t1 on a = 1 when matched then update set b = -b where b = 1 |
| +> when not matched then insert values (3,3); |
| |
| --- 1 row(s) updated. |
| >>select * from t015t1; |
| |
| A B |
| ----------- ----------- |
| |
| 1 -1 |
| 2 2 |
| |
| --- 2 row(s) selected. |
| >> |
| >>delete from t015t1; |
| |
| --- 2 row(s) deleted. |
| >>merge into t015t1 on a = 1 when matched then update set b = -1 |
| +> when not matched then insert (a) values (1); |
| |
| --- 1 row(s) updated. |
| >>select * from t015t1; |
| |
| A B |
| ----------- ----------- |
| |
| 1 2 |
| |
| --- 1 row(s) selected. |
| >> |
| >>delete from t015t1; |
| |
| --- 1 row(s) deleted. |
| >>merge into t015t1 on a = 1 when not matched then insert values (1,2); |
| |
| --- 1 row(s) updated. |
| >>select * from t015t1; |
| |
| A B |
| ----------- ----------- |
| |
| 1 2 |
| |
| --- 1 row(s) selected. |
| >> |
| >>merge into t015t1 on a = 1 when not matched then insert values (1,2); |
| |
| --- 0 row(s) updated. |
| >>select * from t015t1; |
| |
| A B |
| ----------- ----------- |
| |
| 1 2 |
| |
| --- 1 row(s) selected. |
| >> |
| >>merge into t015t1 on a = 1 when matched then update set b = -1; |
| |
| --- 1 row(s) updated. |
| >>select * from t015t1; |
| |
| A B |
| ----------- ----------- |
| |
| 1 -1 |
| |
| --- 1 row(s) selected. |
| >> |
| >>delete from t015t1; |
| |
| --- 1 row(s) deleted. |
| >>merge into t015t1 on a = 1 when matched then update set b = -1; |
| |
| --- 0 row(s) updated. |
| >>select * from t015t1; |
| |
| --- 0 row(s) selected. |
| >> |
| >>-- merge stmt with params |
| >>delete from t015t1; |
| |
| --- 0 row(s) deleted. |
| >>prepare s from |
| +>merge into t015t1 on a = ? when matched then update set b = ? |
| +> when not matched then insert values (?,?); |
| |
| --- SQL command prepared. |
| >>execute s using 1, -1, 1, 2; |
| |
| --- 1 row(s) updated. |
| >>select * from t015t1; |
| |
| A B |
| ----------- ----------- |
| |
| 1 2 |
| |
| --- 1 row(s) selected. |
| >>execute s using 1, -1, 1, 2; |
| |
| --- 1 row(s) updated. |
| >>select * from t015t1; |
| |
| A B |
| ----------- ----------- |
| |
| 1 -1 |
| |
| --- 1 row(s) selected. |
| >> |
| >>set param ?a 1; |
| >>set param ?b -1; |
| >>set param ?b2 2; |
| >>delete from t015t1; |
| |
| --- 1 row(s) deleted. |
| >>merge into t015t1 on a = ?a when matched then update set b = ?b |
| +> when not matched then insert values (?a,?b2); |
| |
| --- 1 row(s) updated. |
| >>select * from t015t1; |
| |
| A B |
| ----------- ----------- |
| |
| 1 2 |
| |
| --- 1 row(s) selected. |
| >>merge into t015t1 on a = ?a when matched then update set b = ?b |
| +> when not matched then insert values (?a,?b2); |
| |
| --- 1 row(s) updated. |
| >>select * from t015t1; |
| |
| A B |
| ----------- ----------- |
| |
| 1 -1 |
| |
| --- 1 row(s) selected. |
| >>merge into t015t1 on a = ?a when matched then update set b = ?a where b >= ?a |
| +> when not matched then insert values (?a,?b2); |
| |
| --- 0 row(s) updated. |
| >>select * from t015t1; |
| |
| A B |
| ----------- ----------- |
| |
| 1 -1 |
| |
| --- 1 row(s) selected. |
| >>merge into t015t1 on a = ?a when matched then update set b = ?a where b < ?a |
| +> when not matched then insert values (?a,?b2); |
| |
| --- 1 row(s) updated. |
| >>select * from t015t1; |
| |
| A B |
| ----------- ----------- |
| |
| 1 1 |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- merge using rowsets. |
| >>-- prepare only since rowsets are not supported from mxci. |
| >>prepare s from |
| +>merge into t015t1 on a = ?[10] when matched then update set b = ?[10] |
| +> when not matched then insert values (?[10], ?[10]); |
| |
| --- SQL command prepared. |
| >> |
| >>-- merge rows from one table into another |
| >>delete from t015t1; |
| |
| --- 1 row(s) deleted. |
| >>delete from t015t2; |
| |
| --- 0 row(s) deleted. |
| >>insert into t015t1 values (2,2), (4,4); |
| |
| --- 2 row(s) inserted. |
| >>insert into t015t2 values (2,3), (6,7); |
| |
| --- 2 row(s) inserted. |
| >>merge into t015t1 using (select * from t015t2) as t015t2 |
| +> on t015t1.a = t015t2.a |
| +> when matched then update set b = t015t2.b |
| +> when not matched then insert values (t015t2.a, t015t2.b); |
| |
| --- 2 row(s) updated. |
| >>select * from t015t1; |
| |
| A B |
| ----------- ----------- |
| |
| 2 3 |
| 4 4 |
| 6 7 |
| |
| --- 3 row(s) selected. |
| >>merge into t015t1 using (select * from t015t2) as t015t2 |
| +> on t015t1.a = t015t2.a |
| +> when matched then update set b = t015t2.b+1 where b > a |
| +> when not matched then insert values (t015t2.a, t015t2.b); |
| |
| --- 2 row(s) updated. |
| >>select * from t015t1; |
| |
| A B |
| ----------- ----------- |
| |
| 2 4 |
| 4 4 |
| 6 8 |
| |
| --- 3 row(s) selected. |
| >> |
| >>delete from t015t1; |
| |
| --- 3 row(s) deleted. |
| >>delete from t015t2; |
| |
| --- 2 row(s) deleted. |
| >>insert into t015t1 values (2,2), (4,4); |
| |
| --- 2 row(s) inserted. |
| >>insert into t015t2 values (2,3), (6,7); |
| |
| --- 2 row(s) inserted. |
| >>merge into t015t1 using (select * from t015t2) as X(y,z) |
| +> on t015t1.a = X.y |
| +> when matched then update set b = X.z |
| +> when not matched then insert values (X.y, X.z); |
| |
| --- 2 row(s) updated. |
| >>select * from t015t1; |
| |
| A B |
| ----------- ----------- |
| |
| 2 3 |
| 4 4 |
| 6 7 |
| |
| --- 3 row(s) selected. |
| >> |
| >>delete from t015t3; |
| |
| --- 3 row(s) deleted. |
| >>delete from t015t4; |
| |
| --- 0 row(s) deleted. |
| >>insert into t015t3 values (1,1,1), (2,2,2), (3,3,3); |
| |
| --- 3 row(s) inserted. |
| >>insert into t015t4 values (2,3,4), (3,7,9), (4,5,6); |
| |
| --- 3 row(s) inserted. |
| >>merge into t015t3 using (select * from t015t4) as t015t4 |
| +> on t015t3.a = t015t4.a |
| +> when matched then update set (b,c) = (3,4) |
| +> when not matched then insert values (t015t4.a,t015t4.b,t015t4.c); |
| |
| --- 3 row(s) updated. |
| >>select * from t015t3; |
| |
| A B C |
| ----------- ----------- ----------- |
| |
| 1 1 1 |
| 2 3 4 |
| 3 3 4 |
| 4 5 6 |
| |
| --- 4 row(s) selected. |
| >> |
| >>merge into t015t3 on a = 1 |
| +> when matched then update set b = 20, c = 40; |
| |
| --- 1 row(s) updated. |
| >>select * from t015t3; |
| |
| A B C |
| ----------- ----------- ----------- |
| |
| 1 20 40 |
| 2 3 4 |
| 3 3 4 |
| 4 5 6 |
| |
| --- 4 row(s) selected. |
| >> |
| >>-- test fix to bugzilla 3405 |
| >>delete from t015t3; |
| |
| --- 4 row(s) deleted. |
| >>delete from t015t4; |
| |
| --- 3 row(s) deleted. |
| >>insert into t015t3 values (1,1,1), (2,2,2), (3,3,3); |
| |
| --- 3 row(s) inserted. |
| >>insert into t015t4 values (2,3,4), (3,7,9), (4,5,6); |
| |
| --- 3 row(s) inserted. |
| >>merge into t015t3 using (select * from t015t4) as t015t4 |
| +> on t015t3.a = t015t4.a |
| +> when matched then update set (b,c) = (3,4) where b=2 |
| +> when not matched then insert values (t015t4.a,t015t4.b,t015t4.c); |
| |
| --- 2 row(s) updated. |
| >>select * from t015t3; |
| |
| A B C |
| ----------- ----------- ----------- |
| |
| 1 1 1 |
| 2 3 4 |
| 3 3 3 |
| 4 5 6 |
| |
| --- 4 row(s) selected. |
| >> |
| >>delete from t015t3; |
| |
| --- 4 row(s) deleted. |
| >>delete from t015t4; |
| |
| --- 3 row(s) deleted. |
| >>insert into t015t3 values (1,1,1), (2,2,2), (3,3,3); |
| |
| --- 3 row(s) inserted. |
| >>insert into t015t4 values (2,3,4), (3,7,9), (4,5,6); |
| |
| --- 3 row(s) inserted. |
| >>merge into t015t3 using (select * from t015t4) as t015t4 |
| +> on t015t3.a = t015t4.a |
| +> when matched then update set (b,c) = (3,4) where t015t4.a=3 |
| +> when not matched then insert values (t015t4.a,t015t4.b,t015t4.c); |
| |
| --- 2 row(s) updated. |
| >>select * from t015t3; |
| |
| A B C |
| ----------- ----------- ----------- |
| |
| 1 1 1 |
| 2 2 2 |
| 3 3 4 |
| 4 5 6 |
| |
| --- 4 row(s) selected. |
| >> |
| >>-- merge of a joined table into another |
| >>delete from t015t1; |
| |
| --- 3 row(s) deleted. |
| >>delete from t015t2; |
| |
| --- 2 row(s) deleted. |
| >>insert into t015t1 values (2,2), (4,4), (6,8); |
| |
| --- 3 row(s) inserted. |
| >>insert into t015t2 values (2,3), (6,7); |
| |
| --- 2 row(s) inserted. |
| >>delete from t015t3; |
| |
| --- 4 row(s) deleted. |
| >>delete from t015t4; |
| |
| --- 3 row(s) deleted. |
| >>insert into t015t3 values (1,1,1), (2,2,2), (3,3,3); |
| |
| --- 3 row(s) inserted. |
| >>insert into t015t4 values (2,3,4), (3,7,9), (4,5,6); |
| |
| --- 3 row(s) inserted. |
| >>select * from t015t3; |
| |
| A B C |
| ----------- ----------- ----------- |
| |
| 1 1 1 |
| 2 2 2 |
| 3 3 3 |
| |
| --- 3 row(s) selected. |
| >>merge into t015t3 using (select x.a, x.b from t015t1 x, t015t2 y where x.a = y.a) as t(a,b) |
| +> on t015t3.a = t.a |
| +> when matched then update set (b,c) = (b+t.a, c-t.b) |
| +> when not matched then insert values (t.a, t.b, 5); |
| |
| --- 2 row(s) updated. |
| >>select * from t015t3; |
| |
| A B C |
| ----------- ----------- ----------- |
| |
| 1 1 1 |
| 2 4 0 |
| 3 3 3 |
| 6 8 5 |
| |
| --- 4 row(s) selected. |
| >> |
| >>-- reflexive updates |
| >>delete from t015t1; |
| |
| --- 3 row(s) deleted. |
| >>delete from t015t2; |
| |
| --- 2 row(s) deleted. |
| >>insert into t015t1 values (2,2), (4,4), (6,8); |
| |
| --- 3 row(s) inserted. |
| >>insert into t015t2 values (2,3), (6,7); |
| |
| --- 2 row(s) inserted. |
| >>merge into t015t1 on a = 2 when matched then update set b = b+1 |
| +> when not matched then insert values (2,4); |
| |
| --- 1 row(s) updated. |
| >>select * from t015t1; |
| |
| A B |
| ----------- ----------- |
| |
| 2 3 |
| 4 4 |
| 6 8 |
| |
| --- 3 row(s) selected. |
| >>delete from t015t1 where a = 2; |
| |
| --- 1 row(s) deleted. |
| >>merge into t015t1 on a = 2 when matched then update set b = b+1 |
| +> when not matched then insert values (2,4); |
| |
| --- 1 row(s) updated. |
| >>select * from t015t1; |
| |
| A B |
| ----------- ----------- |
| |
| 2 4 |
| 4 4 |
| 6 8 |
| |
| --- 3 row(s) selected. |
| >> |
| >>-- reflexive updates using rowsets |
| >>prepare s from |
| +> Merge into t015t3 on a = ?[10] |
| +> When matched then update set (b,c) = (b + ?[10], ?[10]) |
| +> When not matched then insert values (?[10], ?[10], ?[10]); |
| |
| --- SQL command prepared. |
| >> |
| >>-- reflexive update from one table into another |
| >>delete from t015t3; |
| |
| --- 4 row(s) deleted. |
| >>delete from t015t4; |
| |
| --- 3 row(s) deleted. |
| >>insert into t015t3 values (1,1,1), (2,2,2), (3,3,3); |
| |
| --- 3 row(s) inserted. |
| >>insert into t015t4 values (2,3,4), (3,7,9), (4,5,6); |
| |
| --- 3 row(s) inserted. |
| >>merge into t015t3 using (select * from t015t4) as t4 |
| +> on a = t4.a |
| +> when matched then update set (b,c) = (b+t4.b, t4.c) |
| +> when not matched then insert values (t4.a,t4.b,t4.c); |
| |
| --- 3 row(s) updated. |
| >>select * from t015t3; |
| |
| A B C |
| ----------- ----------- ----------- |
| |
| 1 1 1 |
| 2 5 4 |
| 3 10 9 |
| 4 5 6 |
| |
| --- 4 row(s) selected. |
| >> |
| >>-- explain for update with multi-col |
| >>-- ignored for now due to non-deterministic explain plans |
| >> |
| >>?ignore |
| >>-- before merge stmt was added |
| >>explain options 'f' |
| +>update t015t6 set b = (select a from t015t5 where a = 1), |
| +> c = (select b from t015t5 where a = 1), |
| +> d = (select c from t015t5 where a = 1), |
| +> e = (select d from t015t5 where a = 1), |
| +> f = (select e from t015t5 where a = 1) |
| +> where a = 3; |
| >> |
| >>-- using update with multi-col |
| >>explain options 'f' |
| +>update t015t6 set (b,c,d,e,f) = (select a,b,c,d,e from t015t5 where a = 1) |
| +> where a = 3; |
| >> |
| >>-- using merge stmt |
| >>explain options 'f' |
| +>merge into t015t6 using (select a,b,c,d,e from t015t5 where a = 1) x |
| +> on a = 3 |
| +> when matched then update set (b,c,d,e,f) = (x.a, x.b, x.c, x.d, x.e); |
| >>?ignore |
| >> |
| >>#ifndef SEABASE_REGRESS |
| >>-- embedded tests for rowsets |
| >>-- remove all the products of the make |
| >>sh rm -f etest015.is; |
| >>sh rm -f etest015.c etest015.cpp etest015.exe etest015.ilk etest015.lst etest015.m etest015.obj etest015.pdb; |
| >> |
| >>-- Preprocess, sql compile, C compile application. Redirect output to LOG015. |
| >>sh sh ./makefileall.ksh etest015 ${mxcmpdir} ${mxcidir} 2>&1; |
| >> |
| >>delete from t015t1; |
| >>delete from t015t8; |
| >>delete from t015t10; |
| >>-- run the executable. Redirect output to LOG015 |
| >>sh ./etest015.exe 2>&1 | tee -a LOG015; |
| >> |
| >>select * from t015t1; |
| >> |
| >>select * from t015t8; |
| >>set parserflags 1; |
| >>select * from table (index_table t015t8i1); |
| >>select * from table (index_table t015t8i2); |
| >> |
| >>select * from t015t10; |
| >>select * from table (index_table t015t10i1); |
| >>select * from table (index_table t015t10i2); |
| >>#endif |
| >>#ifdef SEABASE_REGRESS |
| >>delete from t015t1; |
| |
| --- 3 row(s) deleted. |
| >>insert into t015t1 values (1,2),(4,8),(9,18),(2,4),(3,6),(6,12),(8,16),(0,0),(5,10),(7,14); |
| |
| --- 10 row(s) inserted. |
| >> |
| >>delete from t015t10; |
| |
| --- 0 row(s) deleted. |
| >>insert into t015t10 values (10,11,12),(1,2,3),(4,5,6),(9,10,11),(2,3,4),(3,4,5),(6,7,8),(5,6,7),(7,8,9),(8,9,10); |
| |
| --- 10 row(s) inserted. |
| >>#endif |
| >> |
| >>delete from t015t2; |
| |
| --- 2 row(s) deleted. |
| >>merge into t015t2 using (select * from t015t1) x |
| +> on a = x.a and mod (a,2) = 0 |
| +> when matched then update set b = -1 |
| +> when not matched then insert values (x.a, -2); |
| |
| --- 10 row(s) updated. |
| >>select * from t015t2; |
| |
| A B |
| ----------- ----------- |
| |
| 0 -2 |
| 1 -2 |
| 2 -2 |
| 3 -2 |
| 4 -2 |
| 5 -2 |
| 6 -2 |
| 7 -2 |
| 8 -2 |
| 9 -2 |
| |
| --- 10 row(s) selected. |
| >> |
| >>merge into t015t2 using (select * from t015t1) x |
| +> on a = x.a and mod (a,2) = 0 |
| +> when matched then update set b = -1; |
| |
| --- 5 row(s) updated. |
| >>select * from t015t2; |
| |
| A B |
| ----------- ----------- |
| |
| 0 -1 |
| 1 -2 |
| 2 -1 |
| 3 -2 |
| 4 -1 |
| 5 -2 |
| 6 -1 |
| 7 -2 |
| 8 -1 |
| 9 -2 |
| |
| --- 10 row(s) selected. |
| >> |
| >>merge into t015t2 using (select * from t015t1) x |
| +> on a = x.a and mod (a,2) = 0 |
| +> when not matched then insert values (x.a, -4); |
| |
| *** ERROR[8102] The operation is prevented by a unique constraint. |
| |
| --- 0 row(s) updated. |
| >>select * from t015t2; |
| |
| A B |
| ----------- ----------- |
| |
| 0 -1 |
| 1 -2 |
| 2 -1 |
| 3 -2 |
| 4 -1 |
| 5 -2 |
| 6 -1 |
| 7 -2 |
| 8 -1 |
| 9 -2 |
| |
| --- 10 row(s) selected. |
| >> |
| >>control query default olt_query_opt 'OFF'; |
| |
| --- SQL operation complete. |
| >>delete from t015t2; |
| |
| --- 10 row(s) deleted. |
| >>merge into t015t2 using (select * from t015t1) x |
| +> on a = x.a and mod (a,2) = 0 |
| +> when matched then update set b = -3 |
| +> when not matched then insert values (x.a, -2); |
| |
| --- 10 row(s) updated. |
| >>select * from t015t2; |
| |
| A B |
| ----------- ----------- |
| |
| 0 -2 |
| 1 -2 |
| 2 -2 |
| 3 -2 |
| 4 -2 |
| 5 -2 |
| 6 -2 |
| 7 -2 |
| 8 -2 |
| 9 -2 |
| |
| --- 10 row(s) selected. |
| >> |
| >>merge into t015t2 using (select * from t015t1) x |
| +> on a = x.a and mod (a,2) = 0 |
| +> when matched then update set b = -1; |
| |
| --- 5 row(s) updated. |
| >>select * from t015t2; |
| |
| A B |
| ----------- ----------- |
| |
| 0 -1 |
| 1 -2 |
| 2 -1 |
| 3 -2 |
| 4 -1 |
| 5 -2 |
| 6 -1 |
| 7 -2 |
| 8 -1 |
| 9 -2 |
| |
| --- 10 row(s) selected. |
| >> |
| >>-- non-unique ON clause with merge/update without insert clause |
| >>delete from t015t1; |
| |
| --- 10 row(s) deleted. |
| >>delete from t015t3; |
| |
| --- 4 row(s) deleted. |
| >>insert into t015t1 values (1,1), (2,2); |
| |
| --- 2 row(s) inserted. |
| >>insert into t015t3 values (2,2,3); |
| |
| --- 1 row(s) inserted. |
| >>merge into t015t3 using (select a,b from t015t1) x |
| +> on t015t3.b = x.b |
| +> when matched then update set c = x.b; |
| |
| --- 1 row(s) updated. |
| >>select * from t015t3; |
| |
| A B C |
| ----------- ----------- ----------- |
| |
| 2 2 2 |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- non-unique ON clause with merge/delete without insert clause |
| >>merge into t015t3 using (select a,b from t015t1) x |
| +> on t015t3.b = x.b |
| +> when matched then delete; |
| |
| --- 1 row(s) updated. |
| >>select * from t015t3; |
| |
| --- 0 row(s) selected. |
| >> |
| >>-- IDENTITY column allowed if not part of the primary key |
| >>merge into t015t7 on a = 1 when matched then update set c = 3 |
| +> when not matched then insert (a,c) values (1,2); |
| |
| --- 1 row(s) updated. |
| >>select a,c from t015t7; |
| |
| A C |
| ----------- ----------- |
| |
| 1 2 |
| |
| --- 1 row(s) selected. |
| >>merge into t015t7 on a = 1 when matched then update set c = 3 |
| +> when not matched then insert (a,c) values (1,2); |
| |
| --- 1 row(s) updated. |
| >>select a,c from t015t7; |
| |
| A C |
| ----------- ----------- |
| |
| 1 3 |
| |
| --- 1 row(s) selected. |
| >> |
| >>control query default olt_query_opt 'ON'; |
| |
| --- SQL operation complete. |
| >> |
| >>-- negative cases |
| >>delete from t015t1; |
| |
| --- 2 row(s) deleted. |
| >>delete from t015t3; |
| |
| --- 0 row(s) deleted. |
| >>insert into t015t1 values (1,1), (2,2); |
| |
| --- 2 row(s) inserted. |
| >>insert into t015t3 values (1,1,1), (2,2,2), (3,3,3); |
| |
| --- 3 row(s) inserted. |
| >> |
| >>-- updated columns must be within parens |
| >>update t015t3 set b,c = 10,20; |
| |
| *** ERROR[15001] A syntax error occurred at or before: |
| update t015t3 set b,c = 10,20; |
| ^ (20 characters from start of SQL statement) |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>-- updated values must be within parens |
| >>update t015t3 set (b,c) = 10,20; |
| |
| *** ERROR[15001] A syntax error occurred at or before: |
| update t015t3 set (b,c) = 10,20; |
| ^ (28 characters from start of SQL statement) |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>-- error: only one subquery in the set values clause |
| >>update t015t3 set (b,c) = (10, (select a from t015t1)); |
| |
| *** ERROR[8401] A row subquery or SELECT...INTO statement cannot return more than one row. |
| |
| --- 0 row(s) updated. |
| >> |
| >>-- error: only one subquery in the set values clause |
| >>update t015t3 set (b,c) = ((select a from t015t1), (select a from t015t1)); |
| |
| *** ERROR[8401] A row subquery or SELECT...INTO statement cannot return more than one row. |
| |
| --- 0 row(s) updated. |
| >> |
| >>update t015t5 set (b,c) = (select a,b from t015t1), |
| +> (d,e) = (1,2); |
| |
| *** ERROR[8401] A row subquery or SELECT...INTO statement cannot return more than one row. |
| |
| --- 0 row(s) updated. |
| >> |
| >>-- number of columns should match |
| >>update t015t3 set (b,c) = (select a,b,10 from t015t1); |
| |
| *** ERROR[4023] The degree of each row value constructor (3) must equal the degree of the target table column list (2). |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>-- should return error: more than one rows from the subquery |
| >>update t015t3 set (b,c) = (select a,b from t015t1) |
| +> where a = 3; |
| |
| *** ERROR[8401] A row subquery or SELECT...INTO statement cannot return more than one row. |
| |
| --- 0 row(s) updated. |
| >>update t015t3 set (b,c) = (select a,b from t015t1); |
| |
| *** ERROR[8401] A row subquery or SELECT...INTO statement cannot return more than one row. |
| |
| --- 0 row(s) updated. |
| >> |
| >>-- merge stmt ON clause must be unique if insert clause is specified |
| >>merge into t015t1 on a >= 0 when matched then update set b = -1 |
| +> when not matched then insert values (1,2); |
| |
| *** ERROR[3241] This MERGE statement is not supported. Reason: Non-unique ON clause not allowed with INSERT. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>merge into t015t1 on a = 1 or b = 2 when matched then update set b = -1 |
| +> when not matched then insert values (1,2); |
| |
| *** ERROR[3241] This MERGE statement is not supported. Reason: Non-unique ON clause not allowed with INSERT. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>merge into t015t3 using (select a,b from t015t1) x |
| +> on t015t3.b = x.b |
| +> when matched then update set c = x.b |
| +> when not matched then insert values (1,2,3); |
| |
| *** ERROR[3241] This MERGE statement is not supported. Reason: Non-unique ON clause not allowed with INSERT. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>-- non-unique ON clause with merge/delete not allowed with insert clause |
| >>merge into t015t3 using (select a,b from t015t1) x |
| +> on t015t3.b = x.b |
| +> when matched then delete |
| +> when not matched then insert values (1,2,3); |
| |
| *** ERROR[3241] This MERGE statement is not supported. Reason: MERGE DELETE not allowed with INSERT. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>-- test bugzilla 3424 |
| >>create table temp(a int not null primary key,b int); |
| |
| --- SQL operation complete. |
| >> |
| >>merge into temp on a=1 when matched then update set b=2 where b=1 |
| +>when not matched then insert(a) values(20); |
| |
| --- 1 row(s) updated. |
| >>select * from temp; |
| |
| A B |
| ----------- ----------- |
| |
| 20 ? |
| |
| --- 1 row(s) selected. |
| >> |
| >>insert into temp values(1,1); |
| |
| --- 1 row(s) inserted. |
| >>merge into temp on a=1 when matched then update set b=2 where b=1 |
| +>when not matched then insert(a) values(20); |
| |
| --- 1 row(s) updated. |
| >>select * from temp; |
| |
| A B |
| ----------- ----------- |
| |
| 1 2 |
| 20 ? |
| |
| --- 2 row(s) selected. |
| >> |
| >>drop table temp; |
| |
| --- SQL operation complete. |
| >>create table temp (a int, z int not null, primary key(z)) no partition; |
| |
| --- SQL operation complete. |
| >>merge into temp on a = 1 when matched then update set a = 1; |
| |
| --- 0 row(s) updated. |
| >>merge into temp on a = 1 when not matched then insert values (1, 2); |
| |
| *** ERROR[3241] This MERGE statement is not supported. Reason: Non-unique ON clause not allowed with INSERT. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>merge into temp on a = 1 |
| +> when matched then update set a = 1 |
| +> when not matched then insert values (1, 2); |
| |
| *** ERROR[3241] This MERGE statement is not supported. Reason: Non-unique ON clause not allowed with INSERT. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >> |
| >>-- merge stmt ON clause key must be the same as key value in insert stmt |
| >>delete from t015t1; |
| |
| --- 2 row(s) deleted. |
| >>merge into t015t1 on a = 1 when matched then update set b = -1 |
| +> when not matched then insert values (2,2); |
| |
| --- 1 row(s) updated. |
| >> |
| >>-- subquery not allowed in insert...values clause |
| >>merge into t015t1 using (select * from t015t2) as t015t2 |
| +> on t015t1.a = t015t2.a |
| +> when matched then update set b = t015t2.b |
| +> when not matched then insert values ((select a from t015t2 where a = 1), |
| +> (select b from t015t2 where a = 1)); |
| |
| *** ERROR[3241] This MERGE statement is not supported. Reason: Subquery in INSERT clause not allowed. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>-- subquery not allowed in update...set clause |
| >>merge into t015t3 |
| +> on t015t3.a = 4 |
| +> when matched then update set (b,c) = (select 5,6 from t015t4) |
| +> when not matched then insert values (4,5,6); |
| |
| *** ERROR[3241] This MERGE statement is not supported. Reason: Subquery in SET clause not allowed. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >> |
| >>-- subquery not allowed in ON clause |
| >>merge into t015t1 using (select * from t015t4) x |
| +> on a = (select a from t015t2 where a = 1) |
| +> when matched then update set b = -1 |
| +> when not matched then insert values (1,2); |
| |
| *** ERROR[3241] This MERGE statement is not supported. Reason: Subquery in ON clause not allowed. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>merge into t015t1 on a in (select a from t015t2) |
| +> when matched then update set b = -1 |
| +> when not matched then insert values (1,2); |
| |
| *** ERROR[3241] This MERGE statement is not supported. Reason: Subquery in ON clause not allowed. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>-- aggregate not allowed in "merge ... update where" clause |
| >>merge into t015t1 using (select * from t015t4) x on a = 1 |
| +> when matched then update set b = -1 where max(b) > 0 |
| +> when not matched then insert values (1,2); |
| |
| *** ERROR[3241] This MERGE statement is not supported. Reason: aggregate function in UPDATE ... WHERE clause not allowed. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>merge into t015t1 on a = max(b) |
| +> when matched then update set b = -1 |
| +> when not matched then insert values (1,2); |
| |
| *** ERROR[3241] This MERGE statement is not supported. Reason: aggregate function in ON clause not allowed. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>-- set on rollback not allowed with list specification in SET clause |
| >>update t015t3 set (b,c) = (1,2) set on rollback (b,c) = (-1, -2); |
| |
| *** ERROR[3242] This statement is not supported. Reason: ON ROLLBACK not supported with SET lists. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>update t015t3 set (b,c) = (1,2) set on rollback (b,c) = (select a,b from t015t1); |
| |
| *** ERROR[3242] This statement is not supported. Reason: ON ROLLBACK not supported with SET lists. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>update t015t3 set (b,c) = (1,2) set on rollback b = -2; |
| |
| *** ERROR[3242] This statement is not supported. Reason: ON ROLLBACK not supported with SET lists. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>-- SYSKEY not allowed |
| >>drop table temp; |
| |
| --- SQL operation complete. |
| >>create table temp (a int) no partition; |
| |
| --- SQL operation complete. |
| >>merge into temp on syskey = 1 when matched then update set a = 10; |
| |
| *** ERROR[3241] This MERGE statement is not supported. Reason: SYSKEY not allowed. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>merge into temp on syskey = 1 when not matched then insert values (1); |
| |
| *** ERROR[3241] This MERGE statement is not supported. Reason: SYSKEY not allowed. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>-- IDENTITY column |
| >>drop table temp; |
| |
| --- SQL operation complete. |
| >>create table temp (a largeint generated by default as identity not null not droppable, b int, primary key(a)) no partition; |
| |
| --- SQL operation complete. |
| >>merge into temp on a = 1 when matched then update set b = 10; |
| |
| --- 0 row(s) updated. |
| >>merge into temp on a = 1 when not matched then insert values (1, 2); |
| |
| --- 1 row(s) updated. |
| >> |
| >>-- view |
| >>create view vtemp as select * from t015t1; |
| |
| --- SQL operation complete. |
| >>merge into vtemp on a = 1 when matched then update set b = 10 |
| +> when not matched then insert values (1,2); |
| |
| --- 1 row(s) updated. |
| >> |
| >>-- Merge stmt and unique index |
| >>set parserflags 1; |
| |
| --- SQL operation complete. |
| >>delete from t015t8; |
| |
| --- 0 row(s) deleted. |
| >>insert into t015t8 values (1,2,3); |
| |
| --- 1 row(s) inserted. |
| >>prepare xx from MERGE INTO t015t8 ON i = 2 |
| +> WHEN MATCHED THEN UPDATE SET j = 3 |
| +> WHEN NOT MATCHED THEN INSERT VALUES (2,2,3); |
| |
| --- SQL command prepared. |
| >>execute xx; |
| |
| *** ERROR[8102] The operation is prevented by a unique constraint. |
| |
| --- 0 row(s) updated. |
| >>select * from t015t8; |
| |
| I J K |
| ----------- ----------- ----------- |
| |
| 1 2 3 |
| |
| --- 1 row(s) selected. |
| >>select * from table (index_table t015t8i1); |
| |
| J@ I |
| ----------- ----------- |
| |
| 2 1 |
| |
| --- 1 row(s) selected. |
| >>select * from table (index_table t015t8i2); |
| |
| K@ I |
| ----------- ----------- |
| |
| 3 1 |
| |
| --- 1 row(s) selected. |
| >> |
| >>prepare xx from MERGE INTO t015t8 ON i = 5 |
| +> WHEN MATCHED THEN UPDATE SET j = 6 |
| +> WHEN NOT MATCHED THEN INSERT VALUES (5,6,7); |
| |
| --- SQL command prepared. |
| >>execute xx; |
| |
| --- 1 row(s) updated. |
| >>select * from t015t8; |
| |
| I J K |
| ----------- ----------- ----------- |
| |
| 1 2 3 |
| 5 6 7 |
| |
| --- 2 row(s) selected. |
| >>select * from table (index_table t015t8i1); |
| |
| J@ I |
| ----------- ----------- |
| |
| 2 1 |
| 6 5 |
| |
| --- 2 row(s) selected. |
| >>select * from table (index_table t015t8i2); |
| |
| K@ I |
| ----------- ----------- |
| |
| 3 1 |
| 7 5 |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from MERGE INTO t015t8 ON i = 1 |
| +> WHEN MATCHED THEN UPDATE SET (j,k) = (6,7) |
| +> WHEN NOT MATCHED THEN INSERT VALUES (1,6,7); |
| |
| --- SQL command prepared. |
| >>execute xx; |
| |
| *** ERROR[8102] The operation is prevented by a unique constraint. |
| |
| --- 0 row(s) updated. |
| >>select * from t015t8; |
| |
| I J K |
| ----------- ----------- ----------- |
| |
| 1 2 3 |
| 5 6 7 |
| |
| --- 2 row(s) selected. |
| >>select * from table (index_table t015t8i1); |
| |
| J@ I |
| ----------- ----------- |
| |
| 2 1 |
| 6 5 |
| |
| --- 2 row(s) selected. |
| >>select * from table (index_table t015t8i2); |
| |
| K@ I |
| ----------- ----------- |
| |
| 3 1 |
| 7 5 |
| |
| --- 2 row(s) selected. |
| >> |
| >>prepare xx from MERGE INTO t015t8 ON i = 1 |
| +> WHEN MATCHED THEN UPDATE SET j = 6 |
| +> WHEN NOT MATCHED THEN INSERT VALUES (1,6,7); |
| |
| --- SQL command prepared. |
| >>execute xx; |
| |
| *** ERROR[8102] The operation is prevented by a unique constraint. |
| |
| --- 0 row(s) updated. |
| >>select * from t015t8; |
| |
| I J K |
| ----------- ----------- ----------- |
| |
| 1 2 3 |
| 5 6 7 |
| |
| --- 2 row(s) selected. |
| >>select * from table (index_table t015t8i1); |
| |
| J@ I |
| ----------- ----------- |
| |
| 2 1 |
| 6 5 |
| |
| --- 2 row(s) selected. |
| >>select * from table (index_table t015t8i2); |
| |
| K@ I |
| ----------- ----------- |
| |
| 3 1 |
| 7 5 |
| |
| --- 2 row(s) selected. |
| >> |
| >>insert into t015t9 values(1,6,3); |
| |
| --- 1 row(s) inserted. |
| >>prepare xx from MERGE INTO t015t8 using(select * from t015t9) z(i,j,k) |
| +> ON i = z.i |
| +> WHEN MATCHED THEN UPDATE SET j = z.j |
| +> WHEN NOT MATCHED THEN INSERT VALUES (z.i,z.j,z.k); |
| |
| --- SQL command prepared. |
| >>execute xx; |
| |
| *** ERROR[8102] The operation is prevented by a unique constraint. |
| |
| --- 0 row(s) updated. |
| >>select * from t015t8; |
| |
| I J K |
| ----------- ----------- ----------- |
| |
| 1 2 3 |
| 5 6 7 |
| |
| --- 2 row(s) selected. |
| >>select * from table (index_table t015t8i1); |
| |
| J@ I |
| ----------- ----------- |
| |
| 2 1 |
| 6 5 |
| |
| --- 2 row(s) selected. |
| >>select * from table (index_table t015t8i2); |
| |
| K@ I |
| ----------- ----------- |
| |
| 3 1 |
| 7 5 |
| |
| --- 2 row(s) selected. |
| >> |
| >>delete from t015t8 ; |
| |
| --- 2 row(s) deleted. |
| >>upsert into t015t8 values (1,2,3),(11,12,13) ; |
| |
| --- 2 row(s) inserted. |
| >>select * from t015t8; |
| |
| I J K |
| ----------- ----------- ----------- |
| |
| 1 2 3 |
| 11 12 13 |
| |
| --- 2 row(s) selected. |
| >>select * from table (index_table t015t8i1); |
| |
| J@ I |
| ----------- ----------- |
| |
| 2 1 |
| 12 11 |
| |
| --- 2 row(s) selected. |
| >>select * from table (index_table t015t8i2); |
| |
| K@ I |
| ----------- ----------- |
| |
| 3 1 |
| 13 11 |
| |
| --- 2 row(s) selected. |
| >>delete from t015t8 where i > 10; |
| |
| --- 1 row(s) deleted. |
| >>cqd TRAF_UPSERT_TO_EFF_TREE 'on'; |
| |
| --- SQL operation complete. |
| >>upsert into t015t8 values (1,2,3),(11,12,13) ; |
| |
| --- 2 row(s) inserted. |
| >>cqd TRAF_UPSERT_TO_EFF_TREE reset; |
| |
| --- SQL operation complete. |
| >>select * from t015t8; |
| |
| I J K |
| ----------- ----------- ----------- |
| |
| 1 2 3 |
| 11 12 13 |
| |
| --- 2 row(s) selected. |
| >>select * from table (index_table t015t8i1); |
| |
| J@ I |
| ----------- ----------- |
| |
| 2 1 |
| 12 11 |
| |
| --- 2 row(s) selected. |
| >>select * from table (index_table t015t8i2); |
| |
| K@ I |
| ----------- ----------- |
| |
| 3 1 |
| 13 11 |
| |
| --- 2 row(s) selected. |
| >>MERGE INTO t015t8 ON i = 1 |
| +> WHEN MATCHED THEN UPDATE SET j = 12 |
| +> WHEN NOT MATCHED THEN INSERT VALUES (1,3,4); |
| |
| *** ERROR[8102] The operation is prevented by a unique constraint. |
| |
| --- 0 row(s) updated. |
| >>select * from t015t8; |
| |
| I J K |
| ----------- ----------- ----------- |
| |
| 1 2 3 |
| 11 12 13 |
| |
| --- 2 row(s) selected. |
| >>select * from table (index_table t015t8i1); |
| |
| J@ I |
| ----------- ----------- |
| |
| 2 1 |
| 12 11 |
| |
| --- 2 row(s) selected. |
| >>select * from table (index_table t015t8i2); |
| |
| K@ I |
| ----------- ----------- |
| |
| 3 1 |
| 13 11 |
| |
| --- 2 row(s) selected. |
| >> |
| >> |
| >> |
| >> |
| >>-- Merge stmt and added column. |
| >>alter table t015t11 add column j int default 0 not null; |
| |
| --- SQL operation complete. |
| >>merge into t015t11 on i = 'c' |
| +> when matched then update set j = 4 |
| +> when not matched then insert values ('c', 2); |
| |
| --- 1 row(s) updated. |
| >> |
| >>-- Upsert that gets transformed |
| >>upsert into t015t12 values (1,1,1) ; |
| |
| --- 1 row(s) inserted. |
| >>select * from t015t12; |
| |
| A B C |
| ----------- ----------- ----------- |
| |
| 1 1 1 |
| |
| --- 1 row(s) selected. |
| >>upsert into t015t12 values (1,-1,1) ; |
| |
| --- 1 row(s) inserted. |
| >>select * from t015t12 ; |
| |
| A B C |
| ----------- ----------- ----------- |
| |
| 1 -1 1 |
| |
| --- 1 row(s) selected. |
| >>upsert into t015t12 values (2,2,2),(3,3,3),(1,-1,-1); |
| |
| --- 3 row(s) inserted. |
| >>select * from t015t12 ; |
| |
| A B C |
| ----------- ----------- ----------- |
| |
| 1 -1 -1 |
| 2 2 2 |
| 3 3 3 |
| |
| --- 3 row(s) selected. |
| >>select * from table(index_table t015t12i1) ; |
| |
| B@ _SALT_ A |
| ----------- ---------- ----------- |
| |
| -1 0 1 |
| 2 0 2 |
| 3 1 3 |
| |
| --- 3 row(s) selected. |
| >>select * from table(index_table t015t12i2) ; |
| |
| _SALT_@ C@ A |
| ---------- ----------- ----------- |
| |
| 0 -1 1 |
| 0 2 2 |
| 1 3 3 |
| |
| --- 3 row(s) selected. |
| >> |
| >>upsert into t015t13(b,c) values (1,1); |
| |
| --- 1 row(s) inserted. |
| >>upsert into t015t13(b,c) values (1,2); |
| |
| --- 1 row(s) inserted. |
| >>upsert into t015t13(b,c) values (2,2),(3,3),(4,4); |
| |
| --- 3 row(s) inserted. |
| >>select * from t015t13; |
| |
| A B C |
| -------------------- ----------- ----------- |
| |
| 1 1 1 |
| 2 1 2 |
| 3 2 2 |
| 4 3 3 |
| 5 4 4 |
| |
| --- 5 row(s) selected. |
| >>explain options 'f' upsert into t015t13 (b,c) values (1,1) ; |
| |
| LC RC OP OPERATOR OPT DESCRIPTION CARD |
| ---- ---- ---- -------------------- -------- -------------------- --------- |
| |
| 3 . 4 root o x 1.00E+000 |
| 1 2 3 nested_join 1.00E+000 |
| . . 2 trafodion_insert T015T13I1 1.00E+000 |
| . . 1 trafodion_upsert T015T13 1.00E+000 |
| |
| --- SQL operation complete. |
| >> |
| >>-- Upsert(transformed) on table with RI constraint & unique index |
| >>upsert into t015t14 values (1,2),(11,12); |
| |
| --- 2 row(s) inserted. |
| >>upsert into t015t15 values (2); |
| |
| --- 1 row(s) inserted. |
| >>upsert into t015t14 values (1,3); |
| |
| *** ERROR[8103] The operation is prevented by referential integrity constraint TRAFODION.SCH.CONS1 on table TRAFODION.SCH.T015T14. |
| |
| --- 0 row(s) inserted. |
| >> |
| >>-- Upsert/merge with self-referntial subquery in values list |
| >>delete from t015t7; |
| |
| --- 1 row(s) deleted. |
| >>upsert into t015t7 values (1, NVL((select b from t015t7 where a = 1),DEFAULT),10) ; |
| |
| --- 1 row(s) inserted. |
| >>select * from t015t7; |
| |
| A B C |
| ----------- -------------------- ----------- |
| |
| 1 2 10 |
| |
| --- 1 row(s) selected. |
| >>upsert into t015t7 values (1, NVL((select b+1 from t015t7 where a = 1),DEFAULT),20) ; |
| |
| --- 1 row(s) inserted. |
| >>select * from t015t7; |
| |
| A B C |
| ----------- -------------------- ----------- |
| |
| 1 3 20 |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- guarding against error 7000 and compiler asserts |
| >> |
| >>prepare XX from UPSERT INTO DEC1 |
| +>(ID, codeValue, title , description, path, synonyms, objectClassCode, propertyCode) |
| +>VALUES (NVL((select a.id from DEC1 a where a.codeValue = ?a[10]), DEFAULT), |
| +>?b[10], ?c[10], ?d[10], ?e[10], null, ?f[10], null) ; |
| |
| --- SQL command prepared. |
| >>-- missing default value and default upsert_mode=merge so will transform as merge |
| >>execute explainIt; |
| |
| OPERATOR |
| ---------------- |
| |
| TRAFODION_MERGE |
| |
| --- 1 row(s) selected. |
| >> |
| >>prepare XX from UPSERT INTO DEC1 |
| +>(ID, codeValue, title , description, path, synonyms, objectClassCode, propertyCode) |
| +>VALUES (?a, ?b, ?c, ?d, ?e, null, ?f, null) ; |
| |
| --- SQL command prepared. |
| >>-- missing default value and default upsert_mode=merge so will transform as merge |
| >>execute explainIt; |
| |
| OPERATOR |
| ---------------- |
| |
| TRAFODION_MERGE |
| |
| --- 1 row(s) selected. |
| >> |
| >>prepare XX from UPSERT INTO DEC1 |
| +>(ID, codeValue, title , description, path, synonyms, objectClassCode, propertyCode) |
| +>VALUES (NVL((select a.id from DEC1 a where a.codeValue = ?a), DEFAULT), |
| +>?b, ?c, ?d, ?e, null, ?f, null) ; |
| |
| --- SQL command prepared. |
| >>-- missing default value and default upsert_mode=merge so will transform as merge |
| >>execute explainIt; |
| |
| OPERATOR |
| ---------------- |
| |
| TRAFODION_MERGE |
| |
| --- 1 row(s) selected. |
| >> |
| >>cqd TRAF_UPSERT_MODE 'OPTIMAL'; |
| |
| --- SQL operation complete. |
| >>-- missing default value and default upsert_mode=optimal so will transform as new efficient tree for the next 3 cases |
| >>prepare XX from UPSERT INTO DEC1 |
| +>(ID, codeValue, title , description, path, synonyms, objectClassCode, propertyCode) |
| +>VALUES (NVL((select a.id from DEC1 a where a.codeValue = ?a[10]), DEFAULT), |
| +>?b[10], ?c[10], ?d[10], ?e[10], null, ?f[10], null) ; |
| |
| --- SQL command prepared. |
| >>execute explainItEff; |
| |
| OPERATOR |
| ---------------- |
| |
| SEQUENCE |
| |
| --- 1 row(s) selected. |
| >> |
| >>prepare XX from UPSERT INTO DEC1 |
| +>(ID, codeValue, title , description, path, synonyms, objectClassCode, propertyCode) |
| +>VALUES (?a, ?b, ?c, ?d, ?e, null, ?f, null) ; |
| |
| --- SQL command prepared. |
| >>execute explainItEff; |
| |
| OPERATOR |
| ---------------- |
| |
| SEQUENCE |
| |
| --- 1 row(s) selected. |
| >> |
| >>prepare XX from UPSERT INTO DEC1 |
| +>(ID, codeValue, title , description, path, synonyms, objectClassCode, propertyCode) |
| +>VALUES (NVL((select a.id from DEC1 a where a.codeValue = ?a), DEFAULT), |
| +>?b, ?c, ?d, ?e, null, ?f, null) ; |
| |
| --- SQL command prepared. |
| >>execute explainItEff; |
| |
| OPERATOR |
| ---------------- |
| |
| SEQUENCE |
| |
| --- 1 row(s) selected. |
| >> |
| >>cqd TRAF_UPSERT_MODE reset; |
| |
| --- SQL operation complete. |
| >> |
| >> |
| >>prepare XX from UPSERT INTO DE |
| +>(ID, dataElementConceptID, valueDomainID) VALUES |
| +>(NVL((select a.id from DE a where a.dataElementConceptID = |
| +>(select b.id from DEC1 b where b.codeValue = ?) and a.valueDomainID = ? ), DEFAULT), |
| +>NVL((select d.id from DEC1 d where d.codeValue = ?), NULL), ? ) ; |
| |
| --- SQL command prepared. |
| >>execute explainItEff; |
| |
| OPERATOR |
| ---------------- |
| |
| SEQUENCE |
| |
| --- 1 row(s) selected. |
| >> |
| >>prepare XX from UPSERT INTO ODT |
| +>(ID, objectTypeID, dataElementID, objectCategoryTypeCode) |
| +>VALUES (NVL((select a.id from ODT a where a.objectTypeID = |
| +>(select b.id from OT b where b.objectCode = ? and b.version = ?) and |
| +>a.objectCategoryTypeCode = ? and a.dataElementID = |
| +>(select d.id from DE d where d.dataElementConceptID = |
| +>(select e.id from DEC1 e where e.codeValue = ?) and d.valueDomainID = ? ) ), DEFAULT), |
| +>NVL((select g.id from OT g where g.objectCode = ? and g.version = ?), NULL), |
| +>NVL((select h.id from DE h where h.dataElementConceptID = |
| +>(select i.id from DEC1 i where i.codeValue = ?) and h.valueDomainID = ? ), NULL), ? ); |
| |
| --- SQL command prepared. |
| >>--missing values and traf_mode=merge default transforms into merge |
| >>execute explainIt; |
| |
| OPERATOR |
| ---------------- |
| |
| TRAFODION_MERGE |
| |
| --- 1 row(s) selected. |
| >> |
| >>prepare XX from UPSERT INTO DE |
| +>(ID, dataElementConceptID, valueDomainID) VALUES |
| +>(NVL((select a.id from DE a where a.dataElementConceptID = |
| +>(select b.id from DEC1 b where b.codeValue = ?) and a.valueDomainID = ? ), DEFAULT), |
| +>(select d.id from DEC1 d where d.codeValue = ?), ? ) ; |
| |
| --- SQL command prepared. |
| >>execute explainItEff; |
| |
| OPERATOR |
| ---------------- |
| |
| SEQUENCE |
| |
| --- 1 row(s) selected. |
| >> |
| >>prepare XX from UPSERT INTO DE (ID, dataElementConceptID, valueDomainID) |
| +>VALUES (1, (select d.id from DEC1 d where d.codeValue = ?), 3 ) ; |
| |
| --- SQL command prepared. |
| >>execute explainItEff; |
| |
| OPERATOR |
| ---------------- |
| |
| SEQUENCE |
| |
| --- 1 row(s) selected. |
| >> |
| >>prepare XX from UPSERT INTO DE (ID, dataElementConceptID, valueDomainID) |
| +>VALUES (?[10], ?[10], ?[10] ) ; |
| |
| --- SQL command prepared. |
| >>execute explainItEff; |
| |
| OPERATOR |
| ---------------- |
| |
| SEQUENCE |
| |
| --- 1 row(s) selected. |
| >> |
| >>prepare XX from UPSERT INTO DE (ID, dataElementConceptID, valueDomainID) |
| +>VALUES (1, (select d.id from DEC1 d where d.codeValue = 'aa'), 3 ) ; |
| |
| --- SQL command prepared. |
| >>execute explainItEff; |
| |
| OPERATOR |
| ---------------- |
| |
| SEQUENCE |
| |
| --- 1 row(s) selected. |
| >> |
| >>--upsert transformation to eff tree tests |
| >>cqd traf_upsert_to_eff_tree 'ON'; |
| |
| --- SQL operation complete. |
| >>prepare xx from upsert into t015t16 values (1,2,3,4,5),(6,7,8,9,10); |
| |
| --- SQL command prepared. |
| >>explain options 'f' xx; |
| |
| LC RC OP OPERATOR OPT DESCRIPTION CARD |
| ---- ---- ---- -------------------- -------- -------------------- --------- |
| |
| 15 . 16 root x 4.00E+000 |
| 7 14 15 nested_join 4.00E+000 |
| 10 13 14 merge_union 4.00E+000 |
| 11 12 13 ordered_union 2.00E+000 |
| . . 12 trafodion_insert T015T16I2 1.00E+000 |
| . . 11 trafodion_vsbb_delet T015T16I2 1.00E+000 |
| 8 9 10 ordered_union 2.00E+000 |
| . . 9 trafodion_insert T015T16I1 1.00E+000 |
| . . 8 trafodion_vsbb_delet T015T16I1 1.00E+000 |
| 5 6 7 nested_join 1.00E+000 |
| . . 6 trafodion_vsbb_upser T015T16 1.00E+000 |
| 4 . 5 sequence 1.00E+000 |
| 3 . 4 sort 2.00E+000 |
| 2 1 3 left_hybrid_hash_joi 2.00E+000 |
| . . 2 tuplelist 2.00E+000 |
| . . 1 trafodion_scan T015T16 1.00E+002 |
| |
| --- SQL operation complete. |
| >>execute xx; |
| |
| --- 2 row(s) inserted. |
| >>select * from t015t16 order by a; |
| |
| A B C D E |
| -------------------- ----------- ----------- ----------- ----------- |
| |
| 1 2 3 4 5 |
| 6 7 8 9 10 |
| |
| --- 2 row(s) selected. |
| >>prepare xx2 from upsert into t015t16 values (10,11,12,13,14); |
| |
| --- SQL command prepared. |
| >>execute xx2; |
| |
| --- 1 row(s) inserted. |
| >>select * from t015t16 order by a; |
| |
| A B C D E |
| -------------------- ----------- ----------- ----------- ----------- |
| |
| 1 2 3 4 5 |
| 6 7 8 9 10 |
| 10 11 12 13 14 |
| |
| --- 3 row(s) selected. |
| >> |
| >>prepare xx2 from upsert into t015t16 values (1,2,30,40,50); |
| |
| --- SQL command prepared. |
| >>execute xx2; |
| |
| --- 1 row(s) inserted. |
| >>select * from t015t16 order by a; |
| |
| A B C D E |
| -------------------- ----------- ----------- ----------- ----------- |
| |
| 1 2 30 40 50 |
| 6 7 8 9 10 |
| 10 11 12 13 14 |
| |
| --- 3 row(s) selected. |
| >> |
| >> |
| >>--For single row param input, ensure plan has no vsbb operators |
| >>prepare xx2 from upsert into t015t16 values(?,?,?,?,?); |
| |
| --- SQL command prepared. |
| >>execute explainItVsbb; |
| |
| OPERATOR |
| ------------------------- |
| |
| SEQUENCE |
| |
| --- 1 row(s) selected. |
| >>--For multiple rows, ensure plan has vsbb upsert, vsbb delete |
| >>prepare xx2 from upsert into t015t16 values(?[10],?[10],?[10],?[10],?[10]); |
| |
| --- SQL command prepared. |
| >>execute explainItVsbb; |
| |
| OPERATOR |
| ------------------------- |
| |
| SEQUENCE |
| TRAFODION_VSBB_UPSERT |
| TRAFODION_VSBB_DELETE |
| TRAFODION_VSBB_DELETE |
| |
| --- 4 row(s) selected. |
| >>--For upsert-select, ensure plan has vsbb_upsert and vsbb delete |
| >>prepare xx2 from UPSERT INTO t015t16 select a,b,c,d,e from t015t6 ; |
| |
| --- SQL command prepared. |
| >>execute explainItVsbb; |
| |
| OPERATOR |
| ------------------------- |
| |
| SEQUENCE |
| TRAFODION_VSBB_UPSERT |
| TRAFODION_VSBB_DELETE |
| TRAFODION_VSBB_DELETE |
| |
| --- 4 row(s) selected. |
| >> |
| >>--For multiple values , ensure plan has vsbb upsert and vsbb delete |
| >>prepare xx2 from upsert into t015t16 values (3,4,1,1,1),(6,7,80,90,100),(6,7,81,91,101); |
| |
| --- SQL command prepared. |
| >>execute explainItVsbb; |
| |
| OPERATOR |
| ------------------------- |
| |
| SEQUENCE |
| TRAFODION_VSBB_UPSERT |
| TRAFODION_VSBB_DELETE |
| TRAFODION_VSBB_DELETE |
| |
| --- 4 row(s) selected. |
| >>execute xx2; |
| |
| --- 2 row(s) inserted. |
| >>select a,b from t015t16 order by a; |
| |
| A B |
| -------------------- ----------- |
| |
| 1 2 |
| 3 4 |
| 6 7 |
| 10 11 |
| |
| --- 4 row(s) selected. |
| >> |
| >>cqd traf_upsert_to_eff_tree reset; |
| |
| --- SQL operation complete. |
| >> |
| >>-- merge tests (and insert and update tests) on tables with check constraints |
| >>insert into t015tc1 values ('ACID','FOUR','GUNK'), ('FIVE','FIVE','HIVE'),('TENT','TEXT','TEST'); |
| |
| --- 3 row(s) inserted. |
| >>select * From t015tc1; |
| |
| A B C |
| ---- ---- ---- |
| |
| ACID FOUR GUNK |
| FIVE FIVE HIVE |
| TENT TEXT TEST |
| |
| --- 3 row(s) selected. |
| >> |
| >>insert into t015tc2 values ('ACID','FOUR','GUNK'), ('FIVE','FIVE','HIVE'),('TENT','TEXT','TEST'); |
| |
| --- 3 row(s) inserted. |
| >>select * From t015tc2; |
| |
| A B C |
| ---- ---- ---- |
| |
| ACID FOUR GUNK |
| FIVE FIVE HIVE |
| TENT TEXT TEST |
| |
| --- 3 row(s) selected. |
| >> |
| >>-- should fail, constraint violation |
| >>merge into t015tc1 on a='FIVE' when matched then update set c= 'BALD'; |
| |
| *** ERROR[8101] The operation is prevented by check constraint TRAFODION.SCH.T015TC1_971855625_5413 on table TRAFODION.SCH.T015TC1. |
| |
| --- 0 row(s) updated. |
| >> |
| >>-- should fail, constraint violation |
| >>merge into t015tc1 on a = 'TENT' when matched then update set b = 'ACID'; |
| |
| *** ERROR[8101] The operation is prevented by check constraint TRAFODION.SCH.T015TC1_216755625_5413 on table TRAFODION.SCH.T015TC1. |
| |
| --- 0 row(s) updated. |
| >> |
| >>select * From t015tc1; |
| |
| A B C |
| ---- ---- ---- |
| |
| ACID FOUR GUNK |
| FIVE FIVE HIVE |
| TENT TEXT TEST |
| |
| --- 3 row(s) selected. |
| >> |
| >>-- no constraint violation |
| >>merge into t015tc1 on a='FIVE' when matched then update set c= 'GLAD'; |
| |
| --- 1 row(s) updated. |
| >> |
| >>-- should fail because 'BOMB' < 'FUNK' |
| >>merge into t015tc1 on a = 'NOSH' when matched then update set b = 'GOOD' |
| +> when not matched then insert values ('GOOD','GOOD','BOMB'); |
| |
| *** ERROR[8101] The operation is prevented by check constraint TRAFODION.SCH.T015TC1_971855625_5413 on table TRAFODION.SCH.T015TC1. |
| |
| --- 0 row(s) updated. |
| >> |
| >>-- the row GOOD, GOOD, NICE should successfully be added |
| >>merge into t015tc1 on a = 'NOSH' when matched then update set b = 'GOOD' |
| +> when not matched then insert values ('GOOD','GOOD','NICE'); |
| |
| --- 1 row(s) updated. |
| >> |
| >>select * from t015tc1; |
| |
| A B C |
| ---- ---- ---- |
| |
| ACID FOUR GUNK |
| FIVE FIVE GLAD |
| GOOD GOOD NICE |
| TENT TEXT TEST |
| |
| --- 4 row(s) selected. |
| >> |
| >>-- should fail because of constraint on C |
| >>insert into t015tc1 values ('ACID','TEST','ACID'); |
| |
| *** ERROR[8101] The operation is prevented by check constraint TRAFODION.SCH.T015TC1_971855625_5413 on table TRAFODION.SCH.T015TC1. |
| |
| --- 0 row(s) inserted. |
| >> |
| >>-- should fail because of constraint on B |
| >>insert into t015tc1 values ('ACID','ACID','TEST'); |
| |
| *** ERROR[8101] The operation is prevented by check constraint TRAFODION.SCH.T015TC1_216755625_5413 on table TRAFODION.SCH.T015TC1. |
| |
| --- 0 row(s) inserted. |
| >> |
| >>-- should fail because of constraint on B |
| >>update t015tc1 set a = 'COOL', b = 'ACID' where a = 'TENT'; |
| |
| *** ERROR[8101] The operation is prevented by check constraint TRAFODION.SCH.T015TC1_216755625_5413 on table TRAFODION.SCH.T015TC1. |
| |
| --- 0 row(s) updated. |
| >> |
| >>select * from t015tc1; |
| |
| A B C |
| ---- ---- ---- |
| |
| ACID FOUR GUNK |
| FIVE FIVE GLAD |
| GOOD GOOD NICE |
| TENT TEXT TEST |
| |
| --- 4 row(s) selected. |
| >> |
| >>-- should fail because of constraint on A |
| >>insert into t015tc2 values ('NUMB','NUMB','NUMB'); |
| |
| *** ERROR[8101] The operation is prevented by check constraint TRAFODION.SCH.T015TC2_724165625_5413 on table TRAFODION.SCH.T015TC2. |
| |
| --- 0 row(s) inserted. |
| >> |
| >>-- should fail because of constraint on A |
| >>update t015tc2 set a = 'NUMB', b = 'OKAY' where a = 'TENT'; |
| |
| *** ERROR[8101] The operation is prevented by check constraint TRAFODION.SCH.T015TC2_724165625_5413 on table TRAFODION.SCH.T015TC2. |
| |
| --- 0 row(s) updated. |
| >> |
| >>-- should fail because of constraint on B |
| >>merge into t015tc1 on a = 'NOSH' when matched then update set b = 'ACID'; |
| |
| --- 0 row(s) updated. |
| >> |
| >>select * from t015tc2; |
| |
| A B C |
| ---- ---- ---- |
| |
| ACID FOUR GUNK |
| FIVE FIVE HIVE |
| TENT TEXT TEST |
| |
| --- 3 row(s) selected. |
| >> |
| >> |
| >>log; |
