📄 update.out
字号:
ij> ---- this test is for basic update functionality---- create the tablecreate table t1 (int_col int, smallint_col smallint, char_30_col char(30), varchar_50_col varchar(50));0 rows inserted/updated/deletedij> create table t2 (int_col int, smallint_col smallint, char_30_col char(30), varchar_50_col varchar(50));0 rows inserted/updated/deletedij> -- populate t1insert into t1 values (1, 2, 'char_30_col', 'varchar_50_col');1 row inserted/updated/deletedij> insert into t1 values (null, null, null, null);1 row inserted/updated/deletedij> insert into t2 select * from t1;2 rows inserted/updated/deletedij> select * from t1;INT_COL |SMALL&|CHAR_30_COL |VARCHAR_50_COL ----------------------------------------------------------------------------------------------------1 |2 |char_30_col |varchar_50_col NULL |NULL |NULL |NULL ij> -- update with constantsupdate t1 set int_col = 3, smallint_col = 4, char_30_col = 'CHAR_30_COL', varchar_50_col = 'VARCHAR_50_COL';2 rows inserted/updated/deletedij> select * from t1;INT_COL |SMALL&|CHAR_30_COL |VARCHAR_50_COL ----------------------------------------------------------------------------------------------------3 |4 |CHAR_30_COL |VARCHAR_50_COL 3 |4 |CHAR_30_COL |VARCHAR_50_COL ij> update t1 set varchar_50_col = null, char_30_col = null, smallint_col = null, int_col = null;2 rows inserted/updated/deletedij> select * from t1;INT_COL |SMALL&|CHAR_30_COL |VARCHAR_50_COL ----------------------------------------------------------------------------------------------------NULL |NULL |NULL |NULL NULL |NULL |NULL |NULL ij> update t1 set smallint_col = 6, int_col = 5, varchar_50_col = 'varchar_50_col', char_30_col = 'char_30_col';2 rows inserted/updated/deletedij> select * from t1;INT_COL |SMALL&|CHAR_30_COL |VARCHAR_50_COL ----------------------------------------------------------------------------------------------------5 |6 |char_30_col |varchar_50_col 5 |6 |char_30_col |varchar_50_col ij> -- update columns with column valuesupdate t1 set smallint_col = int_col, int_col = smallint_col, varchar_50_col = char_30_col, char_30_col = varchar_50_col;2 rows inserted/updated/deletedij> select * from t1;INT_COL |SMALL&|CHAR_30_COL |VARCHAR_50_COL ----------------------------------------------------------------------------------------------------6 |5 |varchar_50_col |char_30_col 6 |5 |varchar_50_col |char_30_col ij> update t1 set int_col = int_col, smallint_col = smallint_col, char_30_col = char_30_col, varchar_50_col = varchar_50_col;2 rows inserted/updated/deletedij> select * from t1;INT_COL |SMALL&|CHAR_30_COL |VARCHAR_50_COL ----------------------------------------------------------------------------------------------------6 |5 |varchar_50_col |char_30_col 6 |5 |varchar_50_col |char_30_col ij> -- Negative test - column in SET clause twiceupdate t1 set int_col = 1, int_col = 2;ERROR 42X16: Column name 'INT_COL' appears multiple times in the SET clause of an UPDATE statement.ij> -- Negative test - non-existent column in SET clauseupdate t1 set notacolumn = int_col + 1;ERROR 42X14: 'NOTACOLUMN' is not a column in table or VTI 'APP.T1'.ij> -- target table in source - deferred update---- first, populate tabledelete from t1;2 rows inserted/updated/deletedij> insert into t1 values (1, 1, 'one', 'one');1 row inserted/updated/deletedij> insert into t1 values (2, 2, 'two', 'two');1 row inserted/updated/deletedij> delete from t2;2 rows inserted/updated/deletedij> insert into t2 select * from t1;2 rows inserted/updated/deletedij> autocommit off;ij> select * from t1;INT_COL |SMALL&|CHAR_30_COL |VARCHAR_50_COL ----------------------------------------------------------------------------------------------------1 |1 |one |one 2 |2 |two |two ij> update t1 set int_col = (select t1.int_col from t1, t2 where t1.int_col = t2.int_col and t1.int_col = 1);2 rows inserted/updated/deletedij> select * from t1;INT_COL |SMALL&|CHAR_30_COL |VARCHAR_50_COL ----------------------------------------------------------------------------------------------------1 |1 |one |one 1 |2 |two |two ij> rollback;ij> update t1 set int_col = (select (select int_col from t1 where int_col = 2) from t2 where int_col = 1);2 rows inserted/updated/deletedij> select * from t1;INT_COL |SMALL&|CHAR_30_COL |VARCHAR_50_COL ----------------------------------------------------------------------------------------------------2 |1 |one |one 2 |2 |two |two ij> rollback;ij> update t1 set int_col = (select 1 from t2 where int_col = 2 and 1 in (select int_col from t1) );2 rows inserted/updated/deletedij> select * from t1;INT_COL |SMALL&|CHAR_30_COL |VARCHAR_50_COL ----------------------------------------------------------------------------------------------------1 |1 |one |one 1 |2 |two |two ij> rollback;ij> update t1 set int_col = (select int_col from (select int_col from t1) a where int_col = 2);2 rows inserted/updated/deletedij> select * from t1;INT_COL |SMALL&|CHAR_30_COL |VARCHAR_50_COL ----------------------------------------------------------------------------------------------------2 |1 |one |one 2 |2 |two |two ij> rollback;ij> update t1 set int_col = (select int_col from t2 where int_col = 37 union select int_col from t1 where int_col = 2);2 rows inserted/updated/deletedij> select * from t1;INT_COL |SMALL&|CHAR_30_COL |VARCHAR_50_COL ----------------------------------------------------------------------------------------------------2 |1 |one |one 2 |2 |two |two ij> rollback;ij> 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 );2 rows inserted/updated/deletedij> select * from t1;INT_COL |SMALL&|CHAR_30_COL |VARCHAR_50_COL ----------------------------------------------------------------------------------------------------2 |1 |one |one 2 |2 |two |two ij> rollback;ij> -- single-row deferred updateupdate t1 set int_col = (select int_col from t1 where int_col = 1)where int_col = 2;1 row inserted/updated/deletedij> select * from t1;INT_COL |SMALL&|CHAR_30_COL |VARCHAR_50_COL ----------------------------------------------------------------------------------------------------1 |1 |one |one 1 |2 |two |two ij> rollback;ij> -- zero-row deferred update - degenerate caseupdate t1 set int_col = (select int_col from t1 where int_col = 1)where int_col = 37;0 rows inserted/updated/deletedij> select * from t1;INT_COL |SMALL&|CHAR_30_COL |VARCHAR_50_COL ----------------------------------------------------------------------------------------------------1 |1 |one |one 2 |2 |two |two ij> rollback;ij> autocommit on;ij> -- drop the tabledrop table t1;0 rows inserted/updated/deletedij> drop table t2;0 rows inserted/updated/deletedij> -- Show whether update is statement atomic or notcreate table s (s smallint, i int);0 rows inserted/updated/deletedij> insert into s values (1, 1);1 row inserted/updated/deletedij> insert into s values (1, 65337);1 row inserted/updated/deletedij> insert into s values (1, 1);1 row inserted/updated/deletedij> select * from s;S |I ------------------1 |1 1 |65337 1 |1 ij> -- this should fail and no rows should changeupdate s set s=s+i;ERROR 22003: The resulting value is outside the range for the data type SMALLINT.ij> -- this select should have the same results as the previous one.select * from s;S |I ------------------1 |1 1 |65337 1 |1 ij> -- Show that the table name can be used on the set columnupdate s set s.s=3;3 rows inserted/updated/deletedij> -- and that it must match the target tableupdate s set t.s=4;ERROR 42X55: Table name 'T' should be the same as 'S'.ij> select * from s;S |I ------------------3 |1 3 |65337 3 |1 ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -