positioneddelupd.sql

derby database source code.good for you.

get cursor c9 as 'select * from t1 for update';
next c9;
delete from t1 where current of c9;
update t1 set i = i + 1 where current of c9;
select * from t1;
close c9;

-- restore t1
delete from t1;
insert into t1 select * from t1_copy;
select * from t1;

-- update to row which was subject to searched update
-- (row still within cursor qualification)
get cursor c10 as 'select * from t1 for update';
next c10;
update t1 set i = i + 1;
select * from t1;
update t1 set i = i + 2 where current of c10;
select * from t1;
close c10;

-- restore t1
delete from t1;
insert into t1 select * from t1_copy;
select * from t1;

-- update to row which was subject to searched update
-- (row becomes outside of cursor qualification)
get cursor c10a as 'select * from t1 where i = 1 for update';
next c10a;
update t1 set i = i + 1;
select * from t1;
update t1 set i = i + 2 where current of c10a;
select * from t1;
close c10a;

-- restore t1
delete from t1;
insert into t1 select * from t1_copy;
select * from t1;

-- update to row which was subject to positioned update
-- (row becomes outside of cursor qualification)
get cursor c11 as 'select * from t1 where i = 1 for update';
next c11;
update t1 set i = i + 1 where current of c11;
select * from t1;
update t1 set i = i + 2 where current of c11;
select * from t1;
close c11;

-- restore t1
delete from t1;
insert into t1 select * from t1_copy;
select * from t1;

-- update to row which was subject to 2 searched updates
-- (1st puts row outside of cursor qualification, 2nd restores it)
get cursor c12 as 'select * from t1 where i = 1 for update';
next c12;
update t1 set i = i + 1;
update t1 set i = 1;
select * from t1;
update t1 set i = i + 2 where current of c12;
select * from t1;

-- negative test - try to update a non-existant column
update t1 set notacolumn = i + 1 where current of c12;

close c12;

-- restore t1
delete from t1;
insert into t1 select * from t1_copy;
select * from t1;

-- update column not in SELECT list, but in FOR UPDATE OF list
get cursor c13 as 'select i from t1 for update of v';
next c13;
update t1 set v = '999' where current of c13;
select * from t1;

-- update column not in FOR UPDATE OF list (negative test)
update t1 set i = 999 where current of c13;
select * from t1;
close c13;

-- restore t1
delete from t1;
insert into t1 select * from t1_copy;
select * from t1;

-- update a non-referenced column
get cursor c14 as 'select i from t1 for update';
next c14;
update t1 set v = '999' where current of c14;
select * from t1;
close c14;

-- restore t1
delete from t1;
insert into t1 select * from t1_copy;
select * from t1;

-- .update columns in list in order different from the list's
get cursor c15 as 'select i, v from t1 for update of i, v';
next c15;
update t1 set v = '999', i = 888 where current of c15;
select * from t1;

-- . show that target table name must be used as qualifier, other names not allowed
update t1 set t1.v = '998' where current of c15;
update t1 set t2.v = '997' where current of c15;
select * from t1;
close c15;

-- restore t1
delete from t1;
insert into t1 select * from t1_copy;
select * from t1;

-- .update only 1 column in the list
get cursor c16 as 'select i, v from t1 for update of i, v';
next c16;
update t1 set v = '999' where current of c16;
select * from t1;
close c16;

-- restore t1
delete from t1;
insert into t1 select * from t1_copy;
select * from t1;

-- .try to update through a closed cursor
get cursor c17 as 'select i, v from t1 for update of i, v';
next c17;
close c17;
update t1 set v = '999' where current of c17;
select * from t1;

-- a positioned update requires a named target table.
-- if we prepare the positioned update, close the underlying cursor
-- and reopen it on a different table, then the positioned update
-- should fail 

create table t3(c1 int, c2 int);
insert into t3 values (1,1), (2, 1), (3,3);
create table t4(c1 int, c2 int);
insert into t4 select * from t3;

get cursor c1 as 'select c1 from t3 for update of c1';
next c1;
prepare u1 as 'update t3 set c1 = c1 + 1 where current of c1';
execute u1;
next c1;
select * from t3;

close c1;

get cursor c1 as 'select c1 from t4 for update of c1';
next c1;
execute u1;
select * from t4;
select * from t3;

close c1;

-- now, reopen c1 on a table without column c1 and see
-- what happens on an attempted positioned update
get cursor c1 as 'select * from t1 for update';
next c1;
execute u1;

close c1;

-- now, reopen c1 on t3, but as a read only cursor
select * from t3;
get cursor c1 as 'select c1 from t3 ';
next c1;
execute u1;
select * from t3;

close c1;

-- positioned update on table with index (#724)
create table t5 (c1 int, c2 int);
insert into t5 values (1, 1), (2, 2), (3, 3), (4, 4);
commit;
create index i5 on t5(c1);
get cursor c1 as 'select * from t5 where c1 > 1 for update of c2';
next c1;
update t5 set c2 = 9 where current of c1;
next c1;
next c1;
update t5 set c2 = 9 where current of c1;
select * from t5;
close c1;
rollback;
create index i5 on t5(c2);
get cursor c1 as 'select * from t5 where c1 > 1 for update of c2';
next c1;
update t5 set c2 = 9 where current of c1;
next c1;
next c1;
update t5 set c2 = 9 where current of c1;
select * from t5;
close c1;
rollback;


-- reset autocommit
autocommit on;

-- drop the tables
drop table t1;
drop table t2;
drop table t3;
drop table t4;
drop table t5;
drop table t1_copy;

-- tests for beetle 4417, schema and correlation name not working with
-- current of
create schema ejb;
create table ejb.test1
	(primarykey varchar(41) not null primary key,
	name varchar(200),
	parentkey varchar(41));
insert into ejb.test1 values('0','jack','jill');
autocommit off;

-- test update with schema name
get cursor c1 as 'select primarykey, parentkey, name from ejb.test1 where primarykey = ''0'' for update';
next c1;
prepare p1 as 'update ejb.test1 set name = ''john'' where current of c1';
execute p1;
select primarykey, parentkey, name from ejb.test1;
close c1;

-- test update with schema name and correlation name
get cursor c1 as 'select t1.primarykey, t1.parentkey, t1.name from ejb.test1 t1 where t1.primarykey = ''0'' for update';
next c1;
prepare p1 as 'update ejb.test1 set name = ''joe'' where current of c1';
execute p1;
select primarykey, parentkey, name from ejb.test1;
close c1;

-- test update with set schema
set schema ejb;
get cursor c1 as 'select primarykey, parentkey, name from test1 where primarykey = ''0'' for update';
next c1;
prepare p1 as 'update test1 set name = ''john'' where current of c1';
execute p1;
select primarykey, parentkey, name from ejb.test1;
close c1;

-- test update with set schema and correlation name
get cursor c1 as 'select t1.primarykey, t1.parentkey, t1.name from test1 t1 where t1.primarykey = ''0'' for update';
next c1;
prepare p1 as 'update test1 set name = ''joe'' where current of c1';
execute p1;
select primarykey, parentkey, name from ejb.test1;
close c1;

-- test update with set schema and correlation name and schema name
get cursor c1 as 'select t1.primarykey, t1.parentkey, t1.name from ejb.test1 t1 where t1.primarykey = ''0'' for update';
next c1;
prepare p1 as 'update ejb.test1 set name = ''joe'' where current of c1';
execute p1;
select primarykey, parentkey, name from ejb.test1;
close c1;

-- 
-- reset schema name
set schema app;

-- test delete with schema name 
get cursor c1 as 'select primarykey, parentkey, name from ejb.test1 where primarykey = ''0'' for update';
next c1;
prepare p2 as 'delete from ejb.test1 where current of c1';
execute p2;
select primarykey, parentkey, name from ejb.test1;
close c1;
-- test delete with schema name and correlation name
insert into ejb.test1 values('0','jack','jill');
get cursor c1 as 'select t1.primarykey, t1.parentkey, t1.name from ejb.test1 t1 where t1.primarykey = ''0'' for update';
next c1;
prepare p2 as 'delete from ejb.test1 where current of c1';
execute p2;
select primarykey, parentkey, name from ejb.test1;
close c1;

-- test delete with set schema 
set schema ejb;
insert into test1 values('0','jack','jill');
get cursor c1 as 'select primarykey, parentkey, name from test1 where primarykey = ''0'' for update';
next c1;
prepare p2 as 'delete from test1 where current of c1';
execute p2;
select primarykey, parentkey, name from ejb.test1;
close c1;

-- test delete with set schema and correlation name
insert into test1 values('0','jack','jill');
get cursor c1 as 'select t1.primarykey, t1.parentkey, t1.name from test1 t1 where t1.primarykey = ''0'' for update';
next c1;
prepare p2 as 'delete from test1 where current of c1';
execute p2;
select primarykey, parentkey, name from ejb.test1;
close c1;
-- test delete with set schema and correlation name and schema name
insert into test1 values('0','jack','jill');
get cursor c1 as 'select t1.primarykey, t1.parentkey, t1.name from ejb.test1 t1 where t1.primarykey = ''0'' for update';
next c1;
prepare p2 as 'delete from ejb.test1 where current of c1';
execute p2;
select primarykey, parentkey, name from ejb.test1;
close c1;
commit;

-- clean up
autocommit on;
set schema app;
drop table ejb.test1;
--drop schema ejb restrict; - can't drop this because it will fail SPS tests since
--statements are created and would need to be dropped

-- test correlation on select in current of cursor in current schema
-- this was also broken
create table test1
	(primarykey varchar(41) not null primary key,
	name varchar(200),
	parentkey varchar(41));
-- make sure a cursor will work fine in this situation
insert into test1 values('0','jack','jill');
autocommit off;
get cursor c1 as 'select t1.primarykey, t1.parentkey, t1.name from test1 t1 where t1.primarykey = ''0'' for update';
next c1;
prepare p2 as 'delete from test1 where current of c1';
execute p2;
select primarykey, parentkey, name from test1;
close c1;
commit;

-- clean up
autocommit on;
drop table test1;