github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/dml/update/update.test (about) 1 drop database if exists db1; 2 create database db1; 3 use db1; 4 drop table if exists t1; 5 create table t1 (a int); 6 insert into t1 values(1), (2), (4); 7 drop table if exists t2; 8 create table t2 (b int); 9 insert into t2 values(1), (2), (3); 10 update t1, t2 set a = 1, b =2; 11 select * from t1; 12 update t1, t2 set a = null, b =null; 13 select * from t2; 14 15 drop table if exists t1; 16 drop table if exists t2; 17 create table t1 (a int); 18 insert into t1 values(1), (2), (4); 19 drop table if exists t2; 20 create table t2 (b int); 21 insert into t2 values(1), (2), (3); 22 update t2 as t222, (select b from t2) as t22 set t222.b = 555 where t222.b = 3; 23 select * from t2; 24 25 drop table if exists t1; 26 drop table if exists t2; 27 create table t1 (a int, b int, c int); 28 insert into t1 values(1, 2, 3), (4, 5, 6), (7, 8, 9); 29 create table t2 (a int, b int, c int); 30 insert into t2 values(1, 2, 3), (4, 5, 6), (7, 8, 9); 31 update t1 join t2 on t1.a = t2.a set t1.b = 222, t1.c = 333, t2.b = 222, t2.c = 333; 32 select * from t1; 33 with t11 as (select * from (select * from t1) as t22) update t11 join t2 on t11.a = t2.a set t2.b = 666; 34 select * from t2; 35 36 drop table if exists t1; 37 drop table if exists t2; 38 create table t1 (a int primary key, b int, c int); 39 insert into t1 values(1, 2, 3), (4, 5, 6), (7, 8, 9); 40 create table t2 (a int, b int, c int); 41 insert into t2 values(1, 2, 3), (4, 5, 6), (7, 8, 9); 42 update t1 join t2 on t1.a = t2.a set t1.a = 111 where t1.b = 2; 43 select * from t1; 44 45 drop table if exists t1; 46 create table t1 (a int, b int); 47 insert into t1 values (1, 2), (3, 4), (5, 6); 48 update t1 set a = 1 where a > 1; 49 select * from t1; 50 51 drop table if exists t2; 52 create table t2 (a int primary key, b int); 53 insert into t2 values (1, 2), (3, 4); 54 select * from t2; 55 update t2 set a = 2 where a > 1; 56 select * from t2; 57 update t2 set a = b, b = a +1 where a > 1; 58 select * from t2; 59 60 drop table if exists t3; 61 create table t3 (a char(20)); 62 insert into t3 values("hello"), ("world"); 63 select * from t3; 64 update t3 set a = "modify"; 65 select * from t3; 66 67 drop table if exists t5; 68 create table t5(a date); 69 insert into t5 values ('20070210'), ('1997-02-10'), ('0001-04-28'), ('20041112'), ('0123-04-03'); 70 select * from t5; 71 update t5 set a = '20070212' where a = '20070210'; 72 select * from t5; 73 74 drop table if exists t7; 75 create table t7 (a int, b int, c int); 76 insert into t7 values (1, 2, 11), (3, 4, 11), (5, 6, 11); 77 select * from t7; 78 update t7 set a = b, b = a + 1 where a > 1; 79 select * from t7; 80 81 drop table if exists t8; 82 create table t8 (a int); 83 insert into t8 values(1), (2), (3), (4), (5); 84 select * from t8; 85 update t8 set a = 111 where a > 2 order by a limit 2; 86 select * from t8; 87 88 drop table if exists t9; 89 CREATE TABLE t9 (a bigint(3), b bigint(5) primary key); 90 insert INTO t9 VALUES (1,1),(1,2); 91 update t9 set a=2 where a=1 limit 1; 92 select * from t9; 93 94 drop table if exists t10; 95 create table t10 (a int primary key, b int); 96 insert into t10 values(1, 2), (3, 4), (5, 6); 97 update t10 set b = null, a = a +1 where a > 1; 98 select * from t10; 99 100 drop table if exists t11; 101 create table t11 (a int, b int); 102 insert into t11 values(1, null), (3, 4), (5, null); 103 update t11 set a = b+1; 104 select * from t11; 105 106 drop table if exists t1; 107 create table t1 (a int default 222); 108 insert into t1 values(1), (2), (3); 109 update t1 set a = default; 110 select * from t1; 111 112 drop table if exists t1; 113 drop table if exists t2; 114 create table t1 (a int, b int default 251 + 38); 115 insert into t1 values (1, 1), (2, 2); 116 create table t2 (a int, b int default 111* 3); 117 insert into t2 values (1, 1), (2, 2); 118 update t1 join t2 on t1.a = t2.a set t1.b = default, t2.a = default; 119 select * from t1; 120 select * from t2; 121 122 drop table if exists t1; 123 create table t1(a int auto_increment, b int auto_increment); 124 insert into t1 values(null, null), (null, null); 125 select * from t1; 126 insert into t1 values(100, 200), (null, null); 127 select * from t1; 128 update t1 set a=null; 129 select * from t1; 130 update t1 set b=null; 131 select * from t1; 132 drop table if exists t1; 133 create table t1( 134 id int, 135 a datetime ON UPDATE CURRENT_TIMESTAMP, 136 b datetime NOT NULL DEFAULT CURRENT_TIMESTAMP 137 ); 138 insert into t1(id) values(1); 139 select a is null from t1; 140 update t1 set id = 2 where id = 1; 141 select a is not null from t1; 142 update t1 set id = 3, a = '20121212' where id = 2; 143 select id from t1 where a = '20121212'; 144 145 drop table if exists t1; 146 create table t1(a int, b int, primary key(a)); 147 insert into t1 values(1, 1); 148 update t1 set a = null; 149 150 drop table if exists t1; 151 create table t1(a int, b int, primary key(a, b)); 152 insert into t1 values(1, 1); 153 update t1 set a = null; 154 155 drop table if exists t1; 156 create table t1(a int, b int, primary key(a, b)); 157 insert into t1 values(1, 2); 158 insert into t1 values(1, 3); 159 insert into t1 values(2, 2); 160 insert into t1 values(2, 3); 161 -- @pattern 162 update t1 set a = 2 where a = 1; 163 164 -- @bvt:issue#5790 165 drop table if exists t1; 166 create table t1(a int, b varchar(20), unique key(a)); 167 insert into t1 values(1, '1'); 168 insert into t1 values(2, '2'); 169 insert into t1 values(3, '3'); 170 insert into t1 values(4, '4'); 171 select * from t1; 172 update t1 set a = 2 where a = 1; 173 174 drop table if exists t1; 175 create table t1(a int, b varchar(20), unique key(a, b)); 176 insert into t1 values(1, '2'); 177 insert into t1 values(1, '3'); 178 insert into t1 values(2, '2'); 179 insert into t1 values(2, '3'); 180 select * from t1; 181 update t1 set a = 2 where a = 1; 182 update t1 set a = null where a = 1; 183 -- @bvt:issue 184 185 drop table if exists t1; 186 create table t1(a int, b int, c datetime on update CURRENT_TIMESTAMP); 187 insert into t1(a) values(1); 188 update t1 set a = 2 where a = 1; 189 select c is not null from t1; 190 191 drop table if exists t1; 192 create table t1 (a int primary key, b int); 193 insert into t1 values (1,100); 194 select b from t1 where a = 1 for update; 195 196 drop database if exists db1; 197 create database db1; 198 use db1; 199 create table t1(a int primary key, b int unique key); 200 create database db2; 201 use db2; 202 insert into db1.t1 values (1,1); 203 select * from db1.t1; 204 update db1.t1 set b = 2 where a = 1; 205 select * from db1.t1; 206 delete from db1.t1; 207 select * from db1.t1; 208 drop database if exists db1; 209 drop database if exists db2;