📄 update.out
字号:
ij> -- do some partial updatescreate table t1 (c1 char(250), c2 varchar(100), c3 varchar(100));0 rows inserted/updated/deletedij> insert into t1 values ('a', 'b', 'c');1 row inserted/updated/deletedij> insert into t1 values ('a', 'b', 'c');1 row inserted/updated/deletedij> insert into t1 values ('a', 'b', 'c');1 row inserted/updated/deletedij> insert into t1 values ('a', 'b', 'c');1 row inserted/updated/deletedij> update t1 set c1 = '1st';4 rows inserted/updated/deletedij> select cast(c1 as char(5)), cast(c2 as char(5)), cast(c3 as char(5)) from t1;1 |2 |3 -----------------1st |b |c 1st |b |c 1st |b |c 1st |b |c ij> update t1 set c2 = '2nd';4 rows inserted/updated/deletedij> select cast(c1 as char(5)), cast(c2 as char(5)), cast(c3 as char(5)) from t1;1 |2 |3 -----------------1st |2nd |c 1st |2nd |c 1st |2nd |c 1st |2nd |c ij> update t1 set c3 = '3rd';4 rows inserted/updated/deletedij> select cast(c1 as char(5)), cast(c2 as char(5)), cast(c3 as char(5)) from t1;1 |2 |3 -----------------1st |2nd |3rd 1st |2nd |3rd 1st |2nd |3rd 1st |2nd |3rd ij> update t1 set c3 = '4th', c2 = '4th';4 rows inserted/updated/deletedij> select cast(c1 as char(5)), cast(c2 as char(5)), cast(c3 as char(5)) from t1;1 |2 |3 -----------------1st |4th |4th 1st |4th |4th 1st |4th |4th 1st |4th |4th ij> update t1 set c1 = '5th', c3 = '5th';4 rows inserted/updated/deletedij> select cast(c1 as char(5)), cast(c2 as char(5)), cast(c3 as char(5)) from t1;1 |2 |3 -----------------5th |4th |5th 5th |4th |5th 5th |4th |5th 5th |4th |5th ij> update t1 set c2 = 'expandingxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';4 rows inserted/updated/deletedij> select cast(c1 as char(5)), cast(c2 as char(5)), cast(c3 as char(5)) from t1;1 |2 |3 -----------------5th |expan|5th 5th |expan|5th 5th |expan|5th 5th |expan|5th ij> update t1 set c3 = 'expandingxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';4 rows inserted/updated/deletedij> select cast(c1 as char(5)), cast(c2 as char(5)), cast(c3 as char(5)) from t1;1 |2 |3 -----------------5th |expan|expan5th |expan|expan5th |expan|expan5th |expan|expanij> update t1 set c2 = 'shrink';4 rows inserted/updated/deletedij> update t1 set c3 = 'shrink';4 rows inserted/updated/deletedij> select cast(c1 as char(5)), cast(c2 as char(5)), cast(c3 as char(5)) from t1;1 |2 |3 -----------------5th |shrin|shrin5th |shrin|shrin5th |shrin|shrin5th |shrin|shrinij> update t1 set c2 = 'expandingxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', c3 = 'expandingxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';4 rows inserted/updated/deletedij> select cast(c1 as char(5)), cast(c2 as char(5)), cast(c3 as char(5)) from t1;1 |2 |3 -----------------5th |expan|expan5th |expan|expan5th |expan|expan5th |expan|expanij> drop table t1;0 rows inserted/updated/deletedij> create table t1 (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int, c9 int);0 rows inserted/updated/deletedij> insert into t1 values (1,2,3,4,5,6,7,8,9);1 row inserted/updated/deletedij> update t1 set c3 = 33, c5 = 55, c6 = 666, c8 = 88;1 row inserted/updated/deletedij> select * from t1;C1 |C2 |C3 |C4 |C5 |C6 |C7 |C8 |C9 -----------------------------------------------------------------------------------------------------------1 |2 |33 |4 |55 |666 |7 |88 |9 ij> update t1 set c9 = 99;1 row inserted/updated/deletedij> select * from t1;C1 |C2 |C3 |C4 |C5 |C6 |C7 |C8 |C9 -----------------------------------------------------------------------------------------------------------1 |2 |33 |4 |55 |666 |7 |88 |99 ij> drop table t1;0 rows inserted/updated/deletedij> ---- 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);0 rows inserted/updated/deletedij> create index ix on x(x);0 rows inserted/updated/deletedij> create index iy on x(y);0 rows inserted/updated/deletedij> insert into x values (1,1),(2,2),(3,3);3 rows inserted/updated/deletedij> autocommit off;ij> prepare p as 'update x set x = x where x = ? and y = ?';ij> execute p using 'values (1,1)';1 row inserted/updated/deletedij> execute p using 'values (2,2)';1 row inserted/updated/deletedij> commit;ij> -- test extra state in update get cursor c1 as 'select * from x for update of x';ij> prepare p1 as 'update x set x = x where current of c1';ij> execute p1;ERROR XCL08: Cursor 'C1' is not on a row.ij> next c1;X |Y -----------------------1 |1 ij> execute p1;1 row inserted/updated/deletedij> next c1;X |Y -----------------------2 |2 ij> next c1;X |Y -----------------------3 |3 ij> execute p1;1 row inserted/updated/deletedij> close c1;ij> execute p1;ERROR XCL07: Cursor 'C1' is closed. Verify that autocommit is OFF.ij> -- clean upautocommit on;ij> drop table x;0 rows inserted/updated/deletedij> -- 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 timecreate table tab1 (c1 int not null primary key, c2 int);0 rows inserted/updated/deletedij> insert into tab1 values (1, 8);1 row inserted/updated/deletedij> autocommit off;ij> -- default read committed isolation levelupdate tab1 set c2 = c2 + 3 where c1 = 1;1 row inserted/updated/deletedij> select type, mode from new org.apache.derby.diag.LockTable() as lockstable where tablename = 'TAB1' order by type;TYPE |MODE----------ROW |X TABLE|IX ij> rollback;ij> -- serializable isolation levelset current isolation to SERIALIZABLE;0 rows inserted/updated/deletedij> update tab1 set c2 = c2 + 3 where c1 = 1;1 row inserted/updated/deletedij> select type, mode from new org.apache.derby.diag.LockTable() as lockstable where tablename = 'TAB1' order by type;TYPE |MODE----------ROW |X TABLE|IX ij> rollback;ij> autocommit on;ij> drop table tab1;0 rows inserted/updated/deletedij> ---- DERBY-1329: Correlated subquery in UPDATE ... SET ... WHERE CURRENT OF--CREATE TABLE BASICTABLE1(ID INTEGER, C3 CHAR(10));0 rows inserted/updated/deletedij> CREATE TABLE BASICTABLE2(IID INTEGER, CC3 CHAR(10));0 rows inserted/updated/deletedij> insert into BASICTABLE1 (C3, ID) values ('abc', 1);1 row inserted/updated/deletedij> insert into BASICTABLE2 (CC3, IID) values ('def', 1);1 row inserted/updated/deletedij> -- Check data.select * from BASICTABLE1;ID |C3 ----------------------1 |abc ij> select * from BASICTABLE2;IID |CC3 ----------------------1 |def ij> autocommit off;ij> get cursor c1 as 'select c3, id from basictable1 for update';ij> next c1;C3 |ID ----------------------abc |1 ij> -- 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;1 row inserted/updated/deletedij> -- Check data; BASICTABLE1 should have been updated.select * from BASICTABLE1;ID |C3 ----------------------1 |def ij> select * from BASICTABLE2;IID |CC3 ----------------------1 |def ij> -- Cleanup.rollback;ij> drop table BASICTABLE1;0 rows inserted/updated/deletedij> drop table BASICTABLE2;0 rows inserted/updated/deletedij> ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -