github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/dml/insert/on_duplicate_key.result (about) 1 create table t1(a int primary key, b int); 2 delete from t1; 3 insert into t1 values (1,1); 4 insert into t1 values (1,2), (2,2) on duplicate key update b=b+10; 5 select * from t1; 6 a b 7 1 11 8 2 2 9 delete from t1; 10 insert into t1 values (1,1); 11 insert into t1 values (1,2), (2,2) on duplicate key update b=values(b)+10; 12 select * from t1; 13 a b 14 1 12 15 2 2 16 delete from t1; 17 insert into t1 values (1,1); 18 insert into t1 values (1,11), (2,22), (3,33) on duplicate key update a=a+1,b=100; 19 select * from t1; 20 a b 21 4 100 22 delete from t1; 23 insert into t1 values (1,1); 24 insert into t1 values (1,2), (1,22) on duplicate key update b=b+10; 25 select * from t1; 26 a b 27 1 21 28 delete from t1; 29 insert into t1 values (1,1),(3,3); 30 insert into t1 values (1,2),(2,22) on duplicate key update a=a+1; 31 Duplicate entry '3' for key 'a' 32 delete from t1; 33 insert into t1 values (1,1),(3,3); 34 insert into t1 values (1,2),(2,22),(3,33) on duplicate key update a=a+1; 35 constraint violation: Duplicate entry for key 'a' 36 drop table t1; 37 create table t1(a int, b int, c int, primary key(a, b)); 38 delete from t1; 39 insert into t1 values (1,1,1); 40 insert into t1 values (1,1,2), (2,2,2) on duplicate key update c=c+10; 41 select * from t1; 42 a b c 43 1 1 11 44 2 2 2 45 delete from t1; 46 insert into t1 values (1,1,1); 47 insert into t1 values (1,1,2), (2,2,2) on duplicate key update c=values(c)+10; 48 select * from t1; 49 a b c 50 1 1 12 51 2 2 2 52 drop table t1; 53 create table t1(a int unique key, b int); 54 delete from t1; 55 insert into t1 values (1,1); 56 insert into t1 values (1,2), (2,2) on duplicate key update b=b+10; 57 select * from t1; 58 a b 59 1 11 60 2 2 61 delete from t1; 62 insert into t1 values (1,1); 63 insert into t1 values (1,2), (2,2) on duplicate key update b=values(b)+10; 64 select * from t1; 65 a b 66 1 12 67 2 2 68 delete from t1; 69 insert into t1 values (1,1); 70 insert into t1 values (1,11), (2,22), (3,33) on duplicate key update a=a+1,b=100; 71 select * from t1; 72 a b 73 4 100 74 delete from t1; 75 insert into t1 values (1,1); 76 insert into t1 values (1,2), (1,22) on duplicate key update b=b+10; 77 select * from t1; 78 a b 79 1 21 80 delete from t1; 81 insert into t1 values (1,1),(3,3); 82 insert into t1 values (1,2),(2,22) on duplicate key update a=a+1; 83 Duplicate entry '3' for key '__mo_index_idx_col' 84 delete from t1; 85 insert into t1 values (1,1),(3,3); 86 insert into t1 values (1,2),(2,22),(3,33) on duplicate key update a=a+1; 87 constraint violation: Duplicate entry for key 'a' 88 drop table t1; 89 create table t1(a int, b int, c int, unique key(a, b)); 90 delete from t1; 91 insert into t1 values (1,1,1); 92 insert into t1 values (1,1,2), (2,2,2) on duplicate key update c=c+10; 93 select * from t1; 94 a b c 95 1 1 11 96 2 2 2 97 delete from t1; 98 insert into t1 values (1,1,1); 99 insert into t1 values (1,1,2), (2,2,2) on duplicate key update c=values(c)+10; 100 select * from t1; 101 a b c 102 1 1 12 103 2 2 2 104 drop table t1; 105 CREATE TABLE IF NOT EXISTS indup_00(`id` INT UNSIGNED,`act_name` VARCHAR(20) NOT NULL,`spu_id` VARCHAR(30) NOT NULL,`uv` BIGINT NOT NULL,`update_time` date default '2020-10-10' COMMENT 'lastest time',unique key idx_act_name_spu_id (act_name,spu_id)); 106 insert into indup_00 values (1,'beijing','001',1,'2021-01-03'),(2,'shanghai','002',2,'2022-09-23'),(3,'guangzhou','003',3,'2022-09-23'); 107 select * from indup_00 order by id; 108 id act_name spu_id uv update_time 109 1 beijing 001 1 2021-01-03 110 2 shanghai 002 2 2022-09-23 111 3 guangzhou 003 3 2022-09-23 112 insert into indup_00 values (6,'shanghai','002',21,'1999-09-23'),(7,'guangzhou','003',31,'1999-09-23') on duplicate key update `act_name`=VALUES(`act_name`), `spu_id`=VALUES(`spu_id`), `uv`=VALUES(`uv`); 113 select * from indup_00 order by id; 114 id act_name spu_id uv update_time 115 1 beijing 001 1 2021-01-03 116 2 shanghai 002 21 2022-09-23 117 3 guangzhou 003 31 2022-09-23 118 drop table indup_00; 119 CREATE TABLE IF NOT EXISTS indup( 120 col1 INT primary key, 121 col2 VARCHAR(20) NOT NULL, 122 col3 VARCHAR(30) NOT NULL, 123 col4 BIGINT default 30 124 ); 125 insert into indup values(22,'11','33',1), (23,'22','55',2),(24,'66','77',1),(25,'99','88',1),(22,'11','33',1) on duplicate key update col1=col1+col2; 126 select * from indup; 127 col1 col2 col3 col4 128 33 11 33 1 129 23 22 55 2 130 24 66 77 1 131 25 99 88 1 132 insert into indup values(24,'1','1',100) on duplicate key update col1=2147483649; 133 Data truncation: data out of range: data type int32, value '2147483649' 134 select * from indup; 135 col1 col2 col3 col4 136 33 11 33 1 137 23 22 55 2 138 24 66 77 1 139 25 99 88 1 140 drop table indup; 141 create table t1(a int primary key, b int, c int); 142 insert into t1 values (1,1,1),(2,2,2); 143 insert into t1 values (1,9,1),(11,8,2) on duplicate key update a=a+10, c=10; 144 select * from t1 order by a; 145 a b c 146 2 2 2 147 21 1 10 148 drop table if exists t1; 149 create table t1(a int primary key, b int unique key); 150 insert into t1 values (1,1),(2,2),(3,3); 151 insert into t1 values (1,20) on duplicate key update b = b + 1; 152 Duplicate entry '2' for key '__mo_index_idx_col' 153 insert into t1 values (20,1) on duplicate key update a = a + 1; 154 Duplicate entry '2' for key 'a' 155 delete from t1; 156 insert into t1 values (1,1),(3,2); 157 insert into t1 values (1,2) on duplicate key update a = 10; 158 delete from t1; 159 insert into t1 values (1,1),(3,2); 160 insert into t1 values (1,2) on duplicate key update a = a+2; 161 Duplicate entry '3' for key 'a'