github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/dml/delete/delete.result (about) 1 drop database if exists db1; 2 drop database if exists db2; 3 create database db1; 4 create database db2; 5 use db2; 6 drop table if exists t1; 7 create table t1 (a int); 8 insert into t1 values (1),(2),(4); 9 use db1; 10 drop table if exists t2; 11 create table t2 (b int); 12 insert into t2 values(1),(2),(3); 13 delete from db1.t2, db2.t1 using db1.t2 join db2.t1 on db1.t2.b = db2.t1.a where 2 > 1; 14 select * from db1.t2; 15 b 16 3 17 select * from db2.t1; 18 a 19 4 20 drop table if exists t1; 21 drop table if exists t2; 22 drop database if exists db1; 23 drop database if exists db2; 24 create database db1; 25 create database db2; 26 use db1; 27 drop table if exists t1; 28 create table t1 (a char(20)); 29 insert into t1 values ('a'), ('b'), ('c'); 30 use db2; 31 drop table if exists t2; 32 create table t2 (b char(20)); 33 insert into t2 values('a'),('b'),('d'); 34 delete from db1.t1, db2.t2 using db1.t1 join db2.t2 on db1.t1.a = db2.t2.b where db1.t1.a = 'a'; 35 select * from db1.t1; 36 a 37 b 38 c 39 select * from db2.t2; 40 b 41 b 42 d 43 drop table if exists t1; 44 drop table if exists t2; 45 drop database if exists db1; 46 drop database if exists db2; 47 create database db1; 48 use db1; 49 drop table if exists t1; 50 drop table if exists t2; 51 create table t1 (a int); 52 insert into t1 values(1), (2), (3); 53 create table t2 (b int); 54 insert into t2 values(1), (2), (3); 55 with t11 as ( select * from t1) delete t2 from t11 join t2 on t11.a = t2.b where t2.b = 3; 56 select * from t2; 57 b 58 1 59 2 60 drop table if exists t1; 61 drop table if exists t2; 62 create table t1 (a int); 63 insert into t1 values(1), (2), (4); 64 create table t2 (b int); 65 insert into t2 values(1), (2), (5); 66 delete t1 from t1 join t2 where t1.a = 2; 67 select * from t1; 68 a 69 1 70 4 71 drop table if exists t1; 72 create table t1 (a int); 73 insert into t1 values(1), (2), (3); 74 delete from t1 as a1 where a1.a = 1; 75 select * from t1; 76 a 77 2 78 3 79 drop table if exists t1; 80 drop table if exists t2; 81 create table t1 (a int); 82 insert into t1 values(1), (2), (4); 83 create table t2 (b int); 84 insert into t2 values(1), (2), (5); 85 DELETE a1, a2 FROM t1 AS a1 INNER JOIN t2 AS a2 WHERE a1.a = a2.b; 86 select * from t1; 87 a 88 4 89 drop table if exists t1; 90 create table t1 (a char(20)); 91 insert into t1 values (null), (null), ('hello'); 92 delete from t1 where a is null; 93 select * from t1; 94 a 95 hello 96 drop table if exists t1; 97 create table t1 (a int, b int); 98 insert into t1 values (1, 2), (3, 4), (5, 6); 99 delete from t1 where a > 1; 100 select * from t1; 101 a b 102 1 2 103 drop table if exists t2; 104 create table t2 (a int primary key, b int); 105 insert into t2 values (1, 2), (3, 4), (5, 6); 106 delete from t2 where a > 1 order by a limit 1; 107 select * from t2; 108 a b 109 1 2 110 5 6 111 drop table if exists t1; 112 create table t1(a int primary key); 113 delete from t1; 114 select * from t1; 115 a 116 drop table if exists t1; 117 create table t1 (a char(20)); 118 insert into t1 values ('heelo'), ('sub'), ('none'), (null); 119 delete from t1 where a is not null; 120 select * from t1; 121 a 122 null 123 drop table if exists t1; 124 drop table if exists t2; 125 create table t1 (a int); 126 insert into t1 values(1), (2), (3); 127 create table t2 (b int primary key); 128 insert into t2 values(1), (2), (3); 129 delete t1, t2 from t1 join t2 on t1.a = t2.b where t1.a = 1; 130 select * from t2; 131 b 132 2 133 3 134 drop table if exists t1; 135 create table t1(a int auto_increment, b bigint auto_increment); 136 insert into t1 values(null, 2), (3, null), (null, null); 137 select * from t1; 138 a b 139 1 2 140 3 3 141 4 4 142 insert into t1 values(100, 2), (null, null), (null, null); 143 select * from t1; 144 a b 145 1 2 146 3 3 147 4 4 148 100 2 149 101 5 150 102 6 151 delete from t1 where a >= 100; 152 select * from t1; 153 a b 154 1 2 155 3 3 156 4 4 157 insert into t1 values(null, null), (null, null), (null, null); 158 select * from t1; 159 a b 160 1 2 161 3 3 162 4 4 163 103 7 164 104 8 165 105 9 166 drop table if exists t1; 167 create table t1(a int, b int, primary key(a, b)); 168 insert into t1 values(1, 2); 169 insert into t1 values(1, 3); 170 insert into t1 values(2, 2); 171 insert into t1 values(2, 3); 172 select * from t1; 173 a b 174 1 2 175 1 3 176 2 2 177 2 3 178 delete from t1 where a = 1; 179 select * from t1; 180 a b 181 2 2 182 2 3 183 drop table if exists t1; 184 create table t1(a int, b int, unique key(a)); 185 insert into t1 values(1, 1); 186 insert into t1 values(2, 2); 187 insert into t1 values(3, 3); 188 insert into t1 values(4, 4); 189 select * from t1; 190 a b 191 1 1 192 2 2 193 3 3 194 4 4 195 delete from t1 where a = 1; 196 select * from t1; 197 a b 198 2 2 199 3 3 200 4 4 201 insert into t1 values(1, 2); 202 drop table if exists t1; 203 create table t1(a int, b int, unique key(a, b)); 204 insert into t1 values(1, 2); 205 insert into t1 values(1, 3); 206 insert into t1 values(2, 2); 207 insert into t1 values(2, 3); 208 select * from t1; 209 a b 210 1 2 211 1 3 212 2 2 213 2 3 214 delete from t1 where a = 1; 215 select * from t1; 216 a b 217 2 2 218 2 3 219 insert into t1 values(1, 2); 220 insert into t1 values(1, null); 221 delete from t1 where a = 1; 222 drop database if exists db1; 223 use `delete`; 224 create table temp(a int); 225 insert into temp select * from generate_series(1,8192) g; 226 create table t(a int); 227 insert into t select * from temp; 228 insert into t select * from t; 229 insert into t select * from t; 230 insert into t select * from t; 231 insert into t select * from t; 232 insert into t select * from t; 233 insert into t select * from t; 234 insert into t select * from t; 235 insert into t select * from t; 236 insert into t select * from t; 237 begin; 238 insert into t select * from t; 239 delete from t where a = 1; 240 select count(*) from t; 241 count(*) 242 8387584 243 rollback; 244 begin; 245 insert into t select * from t; 246 delete from t where a = 1; 247 select count(*) from t; 248 count(*) 249 8387584 250 commit; 251 select count(*) from t; 252 count(*) 253 8387584 254 drop table if exists temp; 255 drop table if exists t; 256 create table temp(a int); 257 insert into temp select * from generate_series(1,8192) g; 258 create table t(a int); 259 insert into t select * from temp; 260 insert into t select * from t; 261 begin; 262 insert into t select * from t; 263 delete from t where a > 1; 264 select count(*) from t; 265 count(*) 266 4 267 rollback; 268 select count(*) from t; 269 count(*) 270 16384 271 begin; 272 insert into t select * from t; 273 delete from t where a > 1; 274 delete from t where a = 1; 275 select count(*) from t; 276 count(*) 277 0 278 commit; 279 select count(*) from t; 280 count(*) 281 0 282 drop table if exists t2; 283 create table t2 (a int, b int unique key, c int, d int, primary key(c,d)); 284 insert into t2 values (1,2,1,2); 285 delete from t2 where b in (c in (select 1) and d in (select 1)); 286 select * from t2; 287 a b c d 288 1 2 1 2 289 drop table if exists t7; 290 create table t7(a int primary key, b int unique key, c varchar(20) unique key); 291 insert into t7 select result, result, "a"||result from generate_series(1,20000) g; 292 select count(*) from t7; 293 count(*) 294 20000 295 delete from t7; 296 select count(*) from t7; 297 count(*) 298 0 299 drop database if exists `delete`; 300 drop database if exists db1; 301 drop database if exists db2; 302 create database db1; 303 create database db2; 304 use db2; 305 drop table if exists t1; 306 create table t1 (a int); 307 insert into t1 values (1),(2),(4); 308 use db1; 309 drop table if exists t2; 310 create table t2 (b int); 311 insert into t2 values(1),(2),(3); 312 delete from db1.t2, db2.t1 using db1.t2 join db2.t1 on db1.t2.b = db2.t1.a; 313 select * from db1.t2; 314 b 315 3 316 select * from db2.t1; 317 a 318 4 319 drop table if exists t3; 320 create table t3 (a int primary key, b int unique key, c int, key(c)); 321 insert into t3 (a, b, c) values (1, 100, 1000); 322 insert into t3 (a, b, c) values (2, 200, 2000); 323 insert into t3 (a, b, c) values (3, 300, 3000); 324 insert into t3 (a, b, c) values (4, 400, 4000); 325 delete from t3 where a > 3; 326 select * from t3; 327 a b c 328 1 100 1000 329 2 200 2000 330 3 300 3000 331 delete from t3 limit 1; 332 select * from t3; 333 a b c 334 2 200 2000 335 3 300 3000 336 delete from t3; 337 select * from t3; 338 a b c 339 drop table if exists t4; 340 create table t4 (a int, b int, c int); 341 insert into t4 (a, b, c) values (1, 100, 1000); 342 insert into t4 (a, b, c) values (2, 200, 2000); 343 insert into t4 (a, b, c) values (3, 300, 3000); 344 insert into t4 (a, b, c) values (4, 400, 4000); 345 delete from t4 where a > 3; 346 select * from t4; 347 a b c 348 1 100 1000 349 2 200 2000 350 3 300 3000 351 delete from t4 limit 1; 352 select * from t4; 353 a b c 354 2 200 2000 355 3 300 3000 356 delete from t4; 357 select * from t4; 358 a b c 359 create table t5 (id int primary key, c int); 360 create table t6 (id int primary key, t5_id int, foreign KEY (t5_id) references t5(id) on delete cascade); 361 insert into t5 (id, c) values (1, 100); 362 insert into t5 (id, c) values (2, 200); 363 insert into t5 (id, c) values (3, 300); 364 insert into t5 (id, c) values (4, 400); 365 insert into t6 (id, t5_id) values (4, 1); 366 insert into t6 (id, t5_id) values (5, 2); 367 insert into t6 (id, t5_id) values (6, 3); 368 insert into t6 (id, t5_id) values (7, 4); 369 delete from t5 where id > 3; 370 select * from t6; 371 id t5_id 372 4 1 373 5 2 374 6 3 375 delete from t5 limit 1; 376 select * from t6; 377 id t5_id 378 5 2 379 6 3 380 delete from t5; 381 select * from t6; 382 id t5_id 383 drop table if exists t6; 384 drop table if exists t5; 385 create table t5 (id int primary key, c int); 386 create table t6 (id int primary key, t5_id int, foreign KEY (t5_id) references t5(id) on delete set null); 387 insert into t5 (id, c) values (1, 100); 388 insert into t5 (id, c) values (2, 200); 389 insert into t5 (id, c) values (3, 300); 390 insert into t5 (id, c) values (4, 400); 391 insert into t6 (id, t5_id) values (4, 1); 392 insert into t6 (id, t5_id) values (5, 2); 393 insert into t6 (id, t5_id) values (6, 3); 394 insert into t6 (id, t5_id) values (7, 4); 395 delete from t5 where id > 3; 396 select * from t6; 397 id t5_id 398 4 1 399 5 2 400 6 3 401 7 null 402 delete from t5 limit 1; 403 select * from t6; 404 id t5_id 405 5 2 406 6 3 407 7 null 408 4 null 409 delete from t5; 410 select * from t6; 411 id t5_id 412 7 null 413 4 null 414 5 null 415 6 null 416 drop table if exists t1; 417 drop table if exists t2; 418 drop table if exists t3; 419 drop table if exists t4; 420 drop table if exists t6; 421 drop table if exists t5; 422 drop database if exists db1; 423 drop database if exists db2;