github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/dml/delete/delete_multiple_table.result (about) 1 DROP TABLE IF EXISTS t1; 2 DROP TABLE IF EXISTS t2; 3 DROP TABLE IF EXISTS t3; 4 DROP TABLE IF EXISTS t4; 5 CREATE TABLE t1( 6 id VARCHAR(10) PRIMARY KEY, 7 name VARCHAR(20) 8 ); 9 CREATE TABLE t2( 10 id VARCHAR(10), 11 sex VARCHAR(4) 12 ); 13 CREATE TABLE t3( 14 name VARCHAR(20), 15 score INT 16 ); 17 CREATE TABLE t4( 18 id VARCHAR(10), 19 class VARCHAR(10) 20 ); 21 INSERT INTO t1 VALUES('1','pet'), ('2','cat'), ('3','dog'), ('4','pig'); 22 INSERT INTO t2 VALUES('1','M'), ('2','F'), ('4','F'), ('6',NULL); 23 INSERT INTO t3 VALUES('pet',101), ('dog',202), ('pig',303), ('dokey',505); 24 INSERT INTO t4 VALUES('2','c2'), ('4','c4'), ('6','c5'), ('7','c1'); 25 SELECT * FROM t1; 26 id name 27 1 pet 28 2 cat 29 3 dog 30 4 pig 31 DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t2.id IS NULL; 32 SELECT * FROM t1; 33 id name 34 1 pet 35 2 cat 36 4 pig 37 SELECT * FROM t2; 38 id sex 39 1 M 40 2 F 41 4 F 42 6 null 43 DELETE t2 FROM t2 LEFT JOIN t1 ON t2.id = t1.id WHERE t2.sex = 'NULL'; 44 SELECT * FROM t2; 45 id sex 46 1 M 47 2 F 48 4 F 49 6 null 50 DELETE t2 FROM t2 LEFT JOIN t1 ON t2.id = t1.id WHERE t2.sex = ''; 51 SELECT * FROM t2; 52 id sex 53 1 M 54 2 F 55 4 F 56 6 null 57 SELECT * FROM t4; 58 id class 59 2 c2 60 4 c4 61 6 c5 62 7 c1 63 DELETE t4 FROM t4 INNER JOIN t1 ON t4.id = t1.id WHERE t4.id BETWEEN 1 AND 3; 64 SELECT * FROM t4; 65 id class 66 4 c4 67 6 c5 68 7 c1 69 SELECT * FROM t3; 70 name score 71 pet 101 72 dog 202 73 pig 303 74 dokey 505 75 DELETE t3 FROM t3 RIGHT JOIN t1 ON t1.name = t3.name WHERE t3.score < 202; 76 SELECT * FROM t3; 77 name score 78 dog 202 79 pig 303 80 dokey 505 81 DELETE t1,t2 FROM t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t1.id = 25; 82 DELETE t1,t2 FROM t1,t2 WHERE t1.id = t2.id AND t1.id = 25; 83 DELETE t1 FROM t1,t2 WHERE t1.id = t2.id AND t1.id = 25; 84 DELETE t2 FROM t1,t2 WHERE t1.id = t2.id AND t1.id = 25; 85 DELETE t2 FROM t1,t2,t3 WHERE t1.id = t2.id OR t1.name = t3.name AND t1.id = 25; 86 DELETE t2 FROM t1,t2,t3,t4 WHERE t1.id = t2.id AND t1.id = t4.id AND t1.id = 25; 87 SELECT * FROM t2; 88 id sex 89 6 null 90 DELETE FROM t1 USING t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t2.id IS NULL; 91 SELECT * FROM t1; 92 id name 93 DELETE FROM t3 USING t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t2.id IS NULL; 94 invalid input: missing FROM-clause entry for table 't3' 95 DELETE t1,t2 FROM table_name AS t1 LEFT JOIN table2_name AS t2 ON t1.id = t2.id WHERE table_name.id = 25; 96 no such table delete_multiple_table.table_name 97 DROP TABLE IF EXISTS t1; 98 DROP TABLE IF EXISTS t2; 99 DROP TABLE IF EXISTS t3; 100 DROP TABLE IF EXISTS t4; 101 CREATE TABLE t1( 102 t1_id INT NOT NULL PRIMARY KEY, 103 t1_o INT 104 ); 105 CREATE TABLE t2( 106 t2_id INT NOT NULL PRIMARY KEY, 107 t1_id INT, 108 t2_o INT 109 ); 110 CREATE TABLE t3( 111 t3_id INT NOT NULL PRIMARY KEY, 112 t2_id INT, 113 t3_o INT 114 ); 115 INSERT INTO t1 VALUES(111,2), (222,3), (333,4); 116 INSERT INTO t2 VALUES(444,111,23), (555,222,34), (666,7,45); 117 INSERT INTO t3 VALUES(777,444,86), (888,666,56), (999,46,31); 118 SELECT * FROM t3; 119 t3_id t2_id t3_o 120 777 444 86 121 888 666 56 122 999 46 31 123 DELETE t3 FROM t1 INNER JOIN t2 ON t1.t1_id = t2.t1_id INNER JOIN t3 ON t2.t2_id = t3.t3_id WHERE t1.t1_id = 222; 124 SELECT * FROM t3; 125 t3_id t2_id t3_o 126 777 444 86 127 888 666 56 128 999 46 31 129 DELETE t1,t2,t3 FROM t1 INNER JOIN t2 ON t1.t1_id = t2.t1_id INNER JOIN t3 ON t2.t2_id = t3.t2_id WHERE t1.t1_id = 111; 130 SELECT * FROM t1; 131 t1_id t1_o 132 222 3 133 333 4 134 SELECT * FROM t2; 135 t2_id t1_id t2_o 136 555 222 34 137 666 7 45 138 SELECT * FROM t3; 139 t3_id t2_id t3_o 140 888 666 56 141 999 46 31 142 DELETE t1,t2,t3 FROM t1 LEFT JOIN t2 ON t1.t1_id = t2.t1_id RIGHT JOIN t3 ON t3.t2_id = t2.t2_id WHERE t2.t2_id = 555; 143 DELETE t1,t2,t3 FROM t1 RIGHT JOIN t2 ON t1.t1_id = t2.t1_id RIGHT JOIN t3 ON t3.t2_id = t2.t2_id WHERE t2.t2_id = 555; 144 DELETE t1,t2,t3 FROM t1 RIGHT JOIN t2 ON t1.t1_id = t2.t1_id LEFT JOIN t3 ON t3.t2_id = t2.t2_id WHERE t2.t2_id = 555; 145 [unknown result because it is related to issue#5219] 146 DELETE t1,t2,t3 FROM t1 LEFT JOIN t2 ON t1.t1_id = t2.t1_id LEFT JOIN t3 ON t3.t2_id = t2.t2_id WHERE t2.t2_id = 555; 147 [unknown result because it is related to issue#5219] 148 DROP TABLE IF EXISTS t1; 149 DROP TABLE IF EXISTS t2; 150 DROP TABLE IF EXISTS t3; 151 CREATE TABLE t1( 152 a INT NOT NULL PRIMARY KEY, 153 b DATE, 154 c DATETIME 155 ); 156 CREATE TABLE t2( 157 a INT NOT NULL PRIMARY KEY, 158 b INT 159 ); 160 CREATE TABLE t3( 161 a VARCHAR(10) NOT NULL PRIMARY KEY, 162 b FLOAT 163 ); 164 CREATE TABLE t4( 165 a CHAR(10) NOT NULL PRIMARY KEY, 166 b DOUBLE 167 ); 168 CREATE TABLE t5( 169 a DOUBLE NOT NULL PRIMARY KEY, 170 b SMALLINT 171 ); 172 INSERT INTO t1 VALUES (1,'2020-06-01','2023-04-05 13:16:46'), (2,'2019-04-19','2021-08-04 16:18:32'),(4,'2028-04-19','2021-08-04 16:18:32'); 173 INSERT INTO t2 VALUES (1,897),(3,7984),(4,4894); 174 INSERT INTO t3 VALUES (3,0.001),(4,9.666),(5,46.321); 175 INSERT INTO t4 VALUES (3,123.11),(4,6464),(9,3641); 176 INSERT INTO t5 VALUES (4,153),(313,1561); 177 DELETE t1,t2,t3,t4,t5 178 FROM t1 179 INNER JOIN t2 ON t1.a = t2.a 180 INNER JOIN t3 ON t2.a = t3.a 181 INNER JOIN t4 ON t3.a = t4.a 182 INNER JOIN t5 ON t4.a = t5.a 183 WHERE t1.a = 1; 184 DELETE t1,t2 FROM t1,t2 WHERE t1.a = t2.a AND t1.a = 1; 185 SELECT * FROM t1; 186 a b c 187 2 2019-04-19 2021-08-04 16:18:32 188 4 2028-04-19 2021-08-04 16:18:32 189 SELECT * FROM t2; 190 a b 191 3 7984 192 4 4894 193 DELETE t1,t5 FROM t1,t5 WHERE t1.a = t5.a AND t1.a = 4; 194 SELECT * FROM t1; 195 a b c 196 2 2019-04-19 2021-08-04 16:18:32 197 SELECT * FROM t5; 198 a b 199 313.0 1561 200 DROP TABLE IF EXISTS t1; 201 DROP TABLE IF EXISTS t2; 202 DROP TABLE IF EXISTS t3; 203 DROP TABLE IF EXISTS t4; 204 DROP TABLE IF EXISTS t5; 205 CREATE TABLE temp( 206 id INT NOT NULL PRIMARY KEY 207 ); 208 CREATE TABLE t1( 209 id INT NOT NULL PRIMARY KEY, 210 name VARCHAR(20) 211 ); 212 INSERT INTO temp VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10); 213 INSERT INTO t1 VALUES(1,'likk'),(2,'fire'),(3,'wikky'); 214 DELETE t1 FROM t1,(SELECT * FROM temp LIMIT 5) AS t WHERE t1.id = t.id AND t1.id > 2; 215 SELECT * FROM t1; 216 id name 217 1 likk 218 2 fire 219 DROP TABLE IF EXISTS t1; 220 DROP TABLE IF EXISTS temp;