github.com/matrixorigin/matrixone@v0.7.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 223 drop database if exists db1;