github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/dml/update/update_multiple_table.result (about) 1 CREATE TABLE product( 2 id VARCHAR(20), 3 product_id VARCHAR(20), 4 product_name VARCHAR(50), 5 price FLOAT 6 ); 7 CREATE TABLE product_price( 8 id VARCHAR(20), 9 product_id VARCHAR(20), 10 price FLOAT 11 ); 12 INSERT INTO product() VALUES ('1', '1001', 'Java教程', '100'); 13 INSERT INTO product() VALUES ('2', '1002', 'MySQL教程', '80'); 14 INSERT INTO product() VALUES ('3', '1003', 'Python教程', '120'); 15 INSERT INTO product() VALUES ('4', '1004', 'C语言教程', '150'); 16 INSERT INTO product_price() VALUES ('1', '1001', NULL); 17 INSERT INTO product_price() VALUES ('2', '1002', NULL); 18 INSERT INTO product_price() VALUES ('3', '1003', NULL); 19 UPDATE product_price, product SET product.id = product.id + 1, product_price.id = product_price.id + 1; 20 SELECT product.id, product_price.id FROM product, product_price; 21 id id 22 2 2 23 2 3 24 2 4 25 3 2 26 3 3 27 3 4 28 4 2 29 4 3 30 4 4 31 5 2 32 5 3 33 5 4 34 UPDATE product, product_price SET product_name = 'PHP', product_price.price = 100 WHERE product.product_id = '1001'; 35 SELECT * FROM product; 36 id product_id product_name price 37 3 1002 MySQL教程 80.0 38 4 1003 Python教程 120.0 39 5 1004 C语言教程 150.0 40 2 1001 PHP 100.0 41 SELECT * FROM product_price; 42 id product_id price 43 2 1001 100.0 44 3 1002 100.0 45 4 1003 100.0 46 UPDATE product p, product_price pp SET pp.price = p.price * 0.8 WHERE p.product_id = pp.product_id; 47 SELECT product.id, product.product_name, product_price.price FROM product, product_price; 48 id product_name price 49 3 MySQL教程 64.0 50 3 MySQL教程 96.0 51 3 MySQL教程 80.0 52 4 Python教程 64.0 53 4 Python教程 96.0 54 4 Python教程 80.0 55 5 C语言教程 64.0 56 5 C语言教程 96.0 57 5 C语言教程 80.0 58 2 PHP 64.0 59 2 PHP 96.0 60 2 PHP 80.0 61 UPDATE product p INNER JOIN product_price pp ON p.product_id = pp.product_id SET pp.price = p.price * 0.8; 62 SELECT product.product_name, product_price.price FROM product, product_price; 63 product_name price 64 MySQL教程 64.0 65 MySQL教程 96.0 66 MySQL教程 80.0 67 Python教程 64.0 68 Python教程 96.0 69 Python教程 80.0 70 C语言教程 64.0 71 C语言教程 96.0 72 C语言教程 80.0 73 PHP 64.0 74 PHP 96.0 75 PHP 80.0 76 DROP TABLE IF EXISTS product; 77 DROP TABLE IF EXISTS product_price; 78 CREATE TABLE stu( 79 id VARCHAR(10), 80 name VARCHAR(20), 81 class_id VARCHAR(5), 82 class_name VARCHAR(10) 83 ); 84 CREATE TABLE class( 85 id VARCHAR(10), 86 name VARCHAR(20), 87 stu_name VARCHAR(20) 88 ); 89 INSERT INTO stu VALUES('1', '张三', '1', NULL), ('2', '李四', '1', NULL), ('3', '王五', '2', NULL); 90 INSERT INTO stu VALUES('4', 'Rose', '2', NULL), ('5', 'Bob', '4', NULL), ('6', 'Ruby', '5', NULL); 91 INSERT INTO class VALUES('1', '一班', NULL), ('2', '二班', NULL), ('3', '三班', NULL); 92 UPDATE stu s , class c SET s.class_name = 'test00', c.stu_name = 'test00' WHERE s.class_id = c.id; 93 SELECT stu.name, stu.class_name, class.name, class.stu_name FROM stu, class; 94 name class_name name stu_name 95 Bob null 三班 null 96 Bob null 一班 test00 97 Bob null 二班 test00 98 Ruby null 三班 null 99 Ruby null 一班 test00 100 Ruby null 二班 test00 101 张三 test00 三班 null 102 张三 test00 一班 test00 103 张三 test00 二班 test00 104 李四 test00 三班 null 105 李四 test00 一班 test00 106 李四 test00 二班 test00 107 王五 test00 三班 null 108 王五 test00 一班 test00 109 王五 test00 二班 test00 110 Rose test00 三班 null 111 Rose test00 一班 test00 112 Rose test00 二班 test00 113 UPDATE stu s INNER JOIN class c ON s.class_id = c.id SET s.class_name = 'test11', c.stu_name = 'test11'; 114 SELECT stu.id, stu.name, stu.class_name, class.stu_name FROM stu, class; 115 id name class_name stu_name 116 5 Bob null null 117 5 Bob null test11 118 5 Bob null test11 119 6 Ruby null null 120 6 Ruby null test11 121 6 Ruby null test11 122 1 张三 test11 null 123 1 张三 test11 test11 124 1 张三 test11 test11 125 2 李四 test11 null 126 2 李四 test11 test11 127 2 李四 test11 test11 128 3 王五 test11 null 129 3 王五 test11 test11 130 3 王五 test11 test11 131 4 Rose test11 null 132 4 Rose test11 test11 133 4 Rose test11 test11 134 UPDATE stu s LEFT JOIN class c ON s.class_id = c.id SET s.class_name = 'test22', c.stu_name = 'test22'; 135 UPDATE stu s RIGHT JOIN class c ON s.class_id = c.id SET s.class_name = 'test33',c.stu_name = 'test33'; 136 SELECT stu.name, stu.class_name, class.stu_name FROM stu, class; 137 name class_name stu_name 138 Bob test22 test33 139 Bob test22 test33 140 Bob test22 test33 141 Ruby test22 test33 142 Ruby test22 test33 143 Ruby test22 test33 144 张三 test33 test33 145 张三 test33 test33 146 张三 test33 test33 147 李四 test33 test33 148 李四 test33 test33 149 李四 test33 test33 150 王五 test33 test33 151 王五 test33 test33 152 王五 test33 test33 153 Rose test33 test33 154 Rose test33 test33 155 Rose test33 test33 156 UPDATE stu s JOIN class c ON s.class_id = c.id SET s.class_name = c.name , c.stu_name = s.name; 157 SELECT stu.name, stu.class_name, class.stu_name FROM stu, class; 158 name class_name stu_name 159 Bob test22 test33 160 Bob test22 张三 161 Bob test22 王五 162 Ruby test22 test33 163 Ruby test22 张三 164 Ruby test22 王五 165 张三 一班 test33 166 张三 一班 张三 167 张三 一班 王五 168 李四 一班 test33 169 李四 一班 张三 170 李四 一班 王五 171 王五 二班 test33 172 王五 二班 张三 173 王五 二班 王五 174 Rose 二班 test33 175 Rose 二班 张三 176 Rose 二班 王五 177 DROP TABLE IF EXISTS stu; 178 DROP TABLE IF EXISTS class; 179 CREATE TABLE t1( 180 id INT, 181 name VARCHAR(20), 182 paytime DATETIME 183 ); 184 CREATE TABLE t2( 185 id INT, 186 processtime DATETIME 187 ); 188 INSERT INTO t1() VALUES(1, 'has', '2022-08-17 19:16:18'), (2, 'norm', '2022-04-12 04:32:46'); 189 INSERT INTO t2() VALUES(1, '2022-08-17 20:23:06'), (2, '2022-04-18 09:19:15'); 190 SELECT * FROM t1; 191 id name paytime 192 1 has 2022-08-17 19:16:18 193 2 norm 2022-04-12 04:32:46 194 SELECT * FROM t2; 195 id processtime 196 1 2022-08-17 20:23:06 197 2 2022-04-18 09:19:15 198 UPDATE t1,t2 SET name = 'tom' WHERE t1.id = '1' AND t2.id = '2'; 199 SELECT t1.id, t1.name FROM t1; 200 id name 201 2 norm 202 1 tom 203 UPDATE t1,t2 SET paytime = '2022-08-16 19:16:18', processtime = '2022-08-16 20:23:06' WHERE t1.id = t2.id AND paytime > '2022-08-15'; 204 UPDATE t1,t2 205 SET 206 paytime = DATE_ADD(paytime, INTERVAL 4 DAY), processtime = DATE_SUB(processtime, INTERVAL 6 DAY) 207 WHERE 208 paytime > '2022-08-25'; 209 SELECT t1.paytime, t2.processtime FROM t1,t2; 210 paytime processtime 211 2022-04-12 04:32:46 2022-04-18 09:19:15 212 2022-04-12 04:32:46 2022-08-16 20:23:06 213 2022-08-16 19:16:18 2022-04-18 09:19:15 214 2022-08-16 19:16:18 2022-08-16 20:23:06 215 UPDATE t1,t2 216 SET 217 paytime = TO_DATE('2099-01-01 00:00:01', '%Y-%m-%d %H-%i-%s'), processtime = TO_DATE('2088-01-01 :00:00:02', '%Y-%m-%d %H:%i:%s') 218 WHERE 219 t1.id = t2.id; 220 UPDATE t1,t2 221 SET 222 paytime = DATE('2088-01-01'), processtime = DATE('9999-01-01') 223 WHERE 224 t1.id = t2.id; 225 SELECT t1.id, t1.paytime, t2.processtime FROM t1, t2; 226 id paytime processtime 227 2 2088-01-01 00:00:00 9999-01-01 00:00:00 228 2 2088-01-01 00:00:00 9999-01-01 00:00:00 229 1 2088-01-01 00:00:00 9999-01-01 00:00:00 230 1 2088-01-01 00:00:00 9999-01-01 00:00:00 231 UPDATE t1,t2 SET paytime = (SELECT DATE('3333-06-06')), processtime = (SELECT DATE('8888-09-09')); 232 SELECT paytime,processtime FROM t1,t2; 233 paytime processtime 234 3333-06-06 00:00:00 8888-09-09 00:00:00 235 3333-06-06 00:00:00 8888-09-09 00:00:00 236 3333-06-06 00:00:00 8888-09-09 00:00:00 237 3333-06-06 00:00:00 8888-09-09 00:00:00 238 DROP TABLE IF EXISTS t1; 239 DROP TABLE IF EXISTS t2; 240 CREATE TABLE t1( 241 str1 CHAR(20) NOT NULL PRIMARY KEY, 242 str2 VARCHAR(20) NOT NULL 243 ); 244 CREATE TABLE t2( 245 str1 CHAR(20), 246 str2 VARCHAR(20) NOT NULL 247 ); 248 INSERT INTO t1 VALUES('this is a test', 'that is actual'), ('1001', 'game of throne'); 249 INSERT INTO t2 VALUES('1001', 'little Iamp'), ('targeran', 'snow and rain'); 250 UPDATE t1,t2 SET t1.str1 = '', t2.str2 = 'NULL' WHERE t1.str1 = t2.str1; 251 SELECT t1.str1, t2.str2 FROM t1,t2; 252 str1 str2 253 this is a test snow and rain 254 this is a test NULL 255 snow and rain 256 NULL 257 UPDATE t1,t2 SET t1.str2 = SUBSTRING('jkfldisaojfd',5), t2.str1 = LENGTH('123'); 258 SELECT t1.str2, t2.str1 FROM t1,t2; 259 str2 str1 260 disaojfd 3 261 disaojfd 3 262 disaojfd 3 263 disaojfd 3 264 UPDATE t1,t2 SET t1.str2 = STARTSWITH(t1.str2,'t'), t2.str2 = STARTSWITH(t2.str2, 's'); 265 SELECT t1.str2, t2.str2 FROM t1,t2; 266 str2 str2 267 0 1 268 0 0 269 0 1 270 0 0 271 DROP TABLE IF EXISTS t1; 272 DROP TABLE IF EXISTS t2; 273 CREATE TABLE t1( 274 n1 INT, 275 n2 INT UNSIGNED, 276 n3 FLOAT 277 ); 278 CREATE TABLE t2( 279 n1 INT, 280 n2 DOUBLE 281 ); 282 INSERT INTO t1 VALUES(1, 2, 3), (5, 6, 7), (10, 11, 12), (12, -0, 19); 283 INSERT INTO t2 VALUES(1, 0), (5, -1), (19, 13); 284 UPDATE t1,t2 SET t1.n2 = EXP(1), t2.n2 = SIN(3) WHERE t1.n1 = t2.n1; 285 SELECT t1.n2, t2.n2 FROM t1, t2; 286 n2 n2 287 11 13.0 288 11 0.1411200080598672 289 11 0.1411200080598672 290 0 13.0 291 0 0.1411200080598672 292 0 0.1411200080598672 293 3 13.0 294 3 0.1411200080598672 295 3 0.1411200080598672 296 3 13.0 297 3 0.1411200080598672 298 3 0.1411200080598672 299 UPDATE t1,t2 SET t1.n2 = POWER(5, -1), t2.n2 = POWER(0,0) WHERE t1.n3 BETWEEN 0 AND 3; 300 SELECT t1.n2, t2.n2 FROM t1, t2; 301 n2 n2 302 11 1.0 303 11 1.0 304 11 1.0 305 0 1.0 306 0 1.0 307 0 1.0 308 3 1.0 309 3 1.0 310 3 1.0 311 0 1.0 312 0 1.0 313 0 1.0 314 DROP TABLE IF EXISTS t1; 315 DROP TABLE IF EXISTS t2;