github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/dml/update/update.result (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 a 13 1 14 1 15 1 16 update t1, t2 set a = null, b =null; 17 select * from t2; 18 b 19 null 20 null 21 null 22 drop table if exists t1; 23 drop table if exists t2; 24 create table t1 (a int); 25 insert into t1 values(1), (2), (4); 26 drop table if exists t2; 27 create table t2 (b int); 28 insert into t2 values(1), (2), (3); 29 update t2 as t222, (select b from t2) as t22 set t222.b = 555 where t222.b = 3; 30 select * from t2; 31 b 32 1 33 2 34 555 35 drop table if exists t1; 36 drop table if exists t2; 37 create table t1 (a int, b int, c int); 38 insert into t1 values(1, 2, 3), (4, 5, 6), (7, 8, 9); 39 create table t2 (a int, b int, c int); 40 insert into t2 values(1, 2, 3), (4, 5, 6), (7, 8, 9); 41 update t1 join t2 on t1.a = t2.a set t1.b = 222, t1.c = 333, t2.b = 222, t2.c = 333; 42 select * from t1; 43 a b c 44 1 222 333 45 4 222 333 46 7 222 333 47 with t11 as (select * from (select * from t1) as t22) update t11 join t2 on t11.a = t2.a set t2.b = 666; 48 select * from t2; 49 a b c 50 1 666 333 51 4 666 333 52 7 666 333 53 drop table if exists t1; 54 drop table if exists t2; 55 create table t1 (a int primary key, b int, c int); 56 insert into t1 values(1, 2, 3), (4, 5, 6), (7, 8, 9); 57 create table t2 (a int, b int, c int); 58 insert into t2 values(1, 2, 3), (4, 5, 6), (7, 8, 9); 59 update t1 join t2 on t1.a = t2.a set t1.a = 111 where t1.b = 2; 60 select * from t1; 61 a b c 62 4 5 6 63 7 8 9 64 111 2 3 65 drop table if exists t1; 66 create table t1 (a int, b int); 67 insert into t1 values (1, 2), (3, 4), (5, 6); 68 update t1 set a = 1 where a > 1; 69 select * from t1; 70 a b 71 1 2 72 1 4 73 1 6 74 drop table if exists t2; 75 create table t2 (a int primary key, b int); 76 insert into t2 values (1, 2), (3, 4); 77 select * from t2; 78 a b 79 1 2 80 3 4 81 update t2 set a = 2 where a > 1; 82 select * from t2; 83 a b 84 1 2 85 2 4 86 update t2 set a = b, b = a +1 where a > 1; 87 select * from t2; 88 a b 89 1 2 90 4 3 91 drop table if exists t3; 92 create table t3 (a char(20)); 93 insert into t3 values("hello"), ("world"); 94 select * from t3; 95 a 96 hello 97 world 98 update t3 set a = "modify"; 99 select * from t3; 100 a 101 modify 102 modify 103 drop table if exists t5; 104 create table t5(a date); 105 insert into t5 values ('20070210'), ('1997-02-10'), ('0001-04-28'), ('20041112'), ('0123-04-03'); 106 select * from t5; 107 a 108 2007-02-10 109 1997-02-10 110 0001-04-28 111 2004-11-12 112 0123-04-03 113 update t5 set a = '20070212' where a = '20070210'; 114 select * from t5; 115 a 116 1997-02-10 117 0001-04-28 118 2004-11-12 119 0123-04-03 120 2007-02-12 121 drop table if exists t7; 122 create table t7 (a int, b int, c int); 123 insert into t7 values (1, 2, 11), (3, 4, 11), (5, 6, 11); 124 select * from t7; 125 a b c 126 1 2 11 127 3 4 11 128 5 6 11 129 update t7 set a = b, b = a + 1 where a > 1; 130 select * from t7; 131 a b c 132 1 2 11 133 4 4 11 134 6 6 11 135 drop table if exists t8; 136 create table t8 (a int); 137 insert into t8 values(1), (2), (3), (4), (5); 138 select * from t8; 139 a 140 1 141 2 142 3 143 4 144 5 145 update t8 set a = 111 where a > 2 order by a limit 2; 146 select * from t8; 147 a 148 1 149 2 150 5 151 111 152 111 153 drop table if exists t9; 154 CREATE TABLE t9 (a bigint(3), b bigint(5) primary key); 155 insert INTO t9 VALUES (1,1),(1,2); 156 update t9 set a=2 where a=1 limit 1; 157 select * from t9; 158 a b 159 1 2 160 2 1 161 drop table if exists t10; 162 create table t10 (a int primary key, b int); 163 insert into t10 values(1, 2), (3, 4), (5, 6); 164 update t10 set b = null, a = a +1 where a > 1; 165 select * from t10; 166 a b 167 1 2 168 4 null 169 6 null 170 drop table if exists t11; 171 create table t11 (a int, b int); 172 insert into t11 values(1, null), (3, 4), (5, null); 173 update t11 set a = b+1; 174 select * from t11; 175 a b 176 null null 177 5 4 178 null null 179 drop table if exists t1; 180 create table t1 (a int default 222); 181 insert into t1 values(1), (2), (3); 182 update t1 set a = default; 183 select * from t1; 184 a 185 222 186 222 187 222 188 drop table if exists t1; 189 drop table if exists t2; 190 create table t1 (a int, b int default 251 + 38); 191 insert into t1 values (1, 1), (2, 2); 192 create table t2 (a int, b int default 111* 3); 193 insert into t2 values (1, 1), (2, 2); 194 update t1 join t2 on t1.a = t2.a set t1.b = default, t2.a = default; 195 select * from t1; 196 a b 197 1 289 198 2 289 199 select * from t2; 200 a b 201 null 1 202 null 2 203 drop table if exists t1; 204 create table t1(a int auto_increment, b int auto_increment); 205 insert into t1 values(null, null), (null, null); 206 select * from t1; 207 a b 208 1 1 209 2 2 210 insert into t1 values(100, 200), (null, null); 211 select * from t1; 212 a b 213 1 1 214 2 2 215 100 200 216 101 201 217 update t1 set a=null; 218 select * from t1; 219 a b 220 102 1 221 103 2 222 104 200 223 105 201 224 update t1 set b=null; 225 select * from t1; 226 a b 227 102 202 228 103 203 229 104 204 230 105 205 231 drop table if exists t1; 232 create table t1( 233 id int, 234 a datetime ON UPDATE CURRENT_TIMESTAMP, 235 b datetime NOT NULL DEFAULT CURRENT_TIMESTAMP 236 ); 237 insert into t1(id) values(1); 238 select a is null from t1; 239 a is null 240 true 241 update t1 set id = 2 where id = 1; 242 select a is not null from t1; 243 a is not null 244 true 245 update t1 set id = 3, a = '20121212' where id = 2; 246 select id from t1 where a = '20121212'; 247 id 248 3 249 drop table if exists t1; 250 create table t1(a int, b int, primary key(a)); 251 insert into t1 values(1, 1); 252 update t1 set a = null; 253 constraint violation: Column 'a' cannot be null 254 drop table if exists t1; 255 create table t1(a int, b int, primary key(a, b)); 256 insert into t1 values(1, 1); 257 update t1 set a = null; 258 constraint violation: Column 'a' cannot be null 259 drop table if exists t1; 260 create table t1(a int, b int, primary key(a, b)); 261 insert into t1 values(1, 2); 262 insert into t1 values(1, 3); 263 insert into t1 values(2, 2); 264 insert into t1 values(2, 3); 265 update t1 set a = 2 where a = 1; 266 Duplicate entry ('\(\d\,\d\)'|'\d\w\d{5}\w\d{4}') for key '__mo_cpkey_col' 267 drop table if exists t1; 268 create table t1(a int, b varchar(20), unique key(a)); 269 insert into t1 values(1, '1'); 270 insert into t1 values(2, '2'); 271 insert into t1 values(3, '3'); 272 insert into t1 values(4, '4'); 273 select * from t1; 274 a b 275 1 1 276 2 2 277 3 3 278 4 4 279 update t1 set a = 2 where a = 1; 280 tae data: duplicate 281 drop table if exists t1; 282 create table t1(a int, b varchar(20), unique key(a, b)); 283 insert into t1 values(1, '2'); 284 insert into t1 values(1, '3'); 285 insert into t1 values(2, '2'); 286 insert into t1 values(2, '3'); 287 select * from t1; 288 a b 289 1 2 290 1 3 291 2 2 292 2 3 293 update t1 set a = 2 where a = 1; 294 tae data: duplicate 295 update t1 set a = null where a = 1; 296 drop table if exists t1; 297 create table t1(a int, b int, c datetime on update CURRENT_TIMESTAMP); 298 insert into t1(a) values(1); 299 update t1 set a = 2 where a = 1; 300 select c is not null from t1; 301 c is not null 302 true 303 drop table if exists t1; 304 create table t1 (a int primary key, b int); 305 insert into t1 values (1,100); 306 select b from t1 where a = 1 for update; 307 b 308 100 309 drop database if exists db1; 310 create database db1; 311 use db1; 312 create table t1(a int primary key, b int unique key); 313 create database db2; 314 use db2; 315 insert into db1.t1 values (1,1); 316 select * from db1.t1; 317 a b 318 1 1 319 update db1.t1 set b = 2 where a = 1; 320 select * from db1.t1; 321 a b 322 1 2 323 delete from db1.t1; 324 select * from db1.t1; 325 a b 326 drop database if exists db1; 327 drop database if exists db2;