update.sql

derby database source code.good for you.

--
-- this test is for basic update functionality
--



-- create the table
create table t1 (int_col int, smallint_col smallint, char_30_col char(30),
		 varchar_50_col varchar(50));
create table t2 (int_col int, smallint_col smallint, char_30_col char(30),
		 varchar_50_col varchar(50));

-- populate t1
insert into t1 values (1, 2, 'char_30_col', 'varchar_50_col');
insert into t1 values (null, null, null, null);
insert into t2 select * from t1;
select * from t1;

-- update with constants
update t1 set int_col = 3, smallint_col = 4, char_30_col = 'CHAR_30_COL',
	      varchar_50_col = 'VARCHAR_50_COL';
select * from t1;
update t1 set varchar_50_col = null, char_30_col = null, smallint_col = null,
	      int_col = null;
select * from t1;

update t1 set smallint_col = 6, int_col = 5, varchar_50_col = 'varchar_50_col',
	      char_30_col = 'char_30_col';
select * from t1;

-- update columns with column values
update t1 set smallint_col = int_col, int_col = smallint_col,
	      varchar_50_col = char_30_col, char_30_col = varchar_50_col;
select * from t1;
update t1 set int_col = int_col, smallint_col = smallint_col,
	      char_30_col = char_30_col, varchar_50_col = varchar_50_col;
select * from t1;

-- Negative test - column in SET clause twice
update t1 set int_col = 1, int_col = 2;

-- Negative test - non-existent column in SET clause
update t1 set notacolumn = int_col + 1;

-- target table in source - deferred update
--
-- first, populate table
delete from t1;
insert into t1 values (1, 1, 'one', 'one');
insert into t1 values (2, 2, 'two', 'two');
delete from t2;
insert into t2 select * from t1;

autocommit off;

select * from t1;
update t1 set int_col =
	(select t1.int_col
	 from t1, t2
	 where t1.int_col = t2.int_col and t1.int_col = 1);
select * from t1;
rollback;

update t1 set int_col =
	(select
		(select int_col
		 from t1
		 where int_col = 2)
	 from t2
	 where int_col = 1);
select * from t1;
rollback;

update t1 set int_col =
	(select 1
	 from t2
	 where int_col = 2
	 and 1 in
		(select int_col
		 from t1)
	);
select * from t1;
rollback;

update t1 set int_col =
	(select int_col
	 from
		(select int_col
		 from t1) a
	 where int_col = 2);
select * from t1;
rollback;

update t1 set int_col =
	(select int_col
	 from t2
	 where int_col = 37
	union
	 select int_col
	 from t1
	 where int_col = 2);
select * from t1;
rollback;

update t1 set int_col =
	(select int_col
	 from t2
	 where int_col = 37
	union
	 select int_col
	 from
		(select int_col
		 from t1
		 where int_col = 2) a
	);
select * from t1;
rollback;

-- single-row deferred update
update t1 set int_col =
	(select int_col
	 from t1
	 where int_col = 1)
where int_col = 2;
select * from t1;
rollback;

-- zero-row deferred update - degenerate case
update t1 set int_col =
	(select int_col
	 from t1
	 where int_col = 1)
where int_col = 37;
select * from t1;
rollback;

autocommit on;

-- drop the table
drop table t1;
drop table t2;


-- Show whether update is statement atomic or not
create table s (s smallint, i int);
insert into s values (1, 1);
insert into s values (1, 65337);
insert into s values (1, 1);
select * from s;
-- this should fail and no rows should change
update s set s=s+i;
-- this select should have the same results as the previous one.
select * from s;

-- Show that the table name can be used on the set column
update s set s.s=3;
-- and that it must match the target table
update s set t.s=4;
select * from s;

-- do some partial updates
create table t1 (c1 char(250), c2 varchar(100), c3 varchar(100));

insert into t1 values ('a', 'b', 'c');
insert into t1 values ('a', 'b', 'c');
insert into t1 values ('a', 'b', 'c');
insert into t1 values ('a', 'b', 'c');

update t1 set c1 = '1st';
select cast(c1 as char(5)), cast(c2 as char(5)), cast(c3 as char(5)) from t1;
update t1 set c2 = '2nd';
select cast(c1 as char(5)), cast(c2 as char(5)), cast(c3 as char(5)) from t1;
update t1 set c3 = '3rd';
select cast(c1 as char(5)), cast(c2 as char(5)), cast(c3 as char(5)) from t1;
update t1 set c3 = '4th', c2 = '4th';
select cast(c1 as char(5)), cast(c2 as char(5)), cast(c3 as char(5)) from t1;
update t1 set c1 = '5th', c3 = '5th';
select cast(c1 as char(5)), cast(c2 as char(5)), cast(c3 as char(5)) from t1;
update t1 set c2 = 'expandingxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';
select cast(c1 as char(5)), cast(c2 as char(5)), cast(c3 as char(5)) from t1;
update t1 set c3 = 'expandingxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';
select cast(c1 as char(5)), cast(c2 as char(5)), cast(c3 as char(5)) from t1;
update t1 set c2 = 'shrink';
update t1 set c3 = 'shrink';
select cast(c1 as char(5)), cast(c2 as char(5)), cast(c3 as char(5)) from t1;
update t1 set c2 = 'expandingxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
			c3 = 'expandingxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';
select cast(c1 as char(5)), cast(c2 as char(5)), cast(c3 as char(5)) from t1;

drop table t1;
create table t1 (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int, c9 int);
insert into t1 values (1,2,3,4,5,6,7,8,9);
update t1 set c3 = 33, c5 = 55, c6 = 666, c8 = 88;
select * from t1;
update t1 set c9 = 99;
select * from t1;

drop table t1;

--
-- here we test extra state lying around in the
-- deleteResultSet on a prepared statement that
-- is executed multiple times.  if we don't
-- get a nasty error then we are ok
--
create table x (x int, y int);
create index ix on x(x);
create index iy on x(y);
insert into x values (1,1),(2,2),(3,3);
autocommit off;
prepare p as 'update x set x = x where x = ? and y = ?';
execute p using 'values (1,1)';
execute p using 'values (2,2)';
commit;

-- test extra state in update 
get cursor c1 as 'select * from x for update of x';
prepare p1 as 'update x set x = x where current of c1';
execute p1;
next c1;
execute p1;
next c1;
next c1;
execute p1;
close c1;
execute p1;

-- clean up
autocommit on;
drop table x;

-- bug 4318, possible deadlock if table first has IX, then X table lock; make
-- sure you don't have IX table lock and X table lock at the same time

create table tab1 (c1 int not null primary key, c2 int);
insert into tab1 values (1, 8);

autocommit off;

-- default read committed isolation level
update tab1 set c2 = c2 + 3 where c1 = 1;
select type, mode from new org.apache.derby.diag.LockTable() as lockstable where tablename = 'TAB1' order by type;
rollback;

-- serializable isolation level
set current isolation to SERIALIZABLE;
update tab1 set c2 = c2 + 3 where c1 = 1;
select type, mode from new org.apache.derby.diag.LockTable() as lockstable where tablename = 'TAB1' order by type;
rollback;

autocommit on;
drop table tab1;

--
-- DERBY-1329: Correlated subquery in UPDATE ... SET ... WHERE CURRENT OF
--
CREATE TABLE BASICTABLE1(ID INTEGER, C3 CHAR(10));
CREATE TABLE BASICTABLE2(IID INTEGER, CC3 CHAR(10));
insert into BASICTABLE1 (C3, ID) values ('abc', 1);
insert into BASICTABLE2 (CC3, IID) values ('def', 1);

-- Check data.
select * from BASICTABLE1;
select * from BASICTABLE2;

autocommit off;
get cursor c1 as 'select c3, id from basictable1 for update';
next c1;

-- Before fix for DERBY-1329 the following statement would fail with
-- an ASSERT failure or an IndexOutOfBoundsException; after the fix
-- the statement should succeed and the update as well.
update BASICTABLE1 set C3 = (SELECT CC3 FROM BASICTABLE2
  WHERE BASICTABLE1.ID=BASICTABLE2.IID) where current of c1;

-- Check data; BASICTABLE1 should have been updated.
select * from BASICTABLE1;
select * from BASICTABLE2;

-- Cleanup.
rollback;
drop table BASICTABLE1;
drop table BASICTABLE2;