github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/dml/insert/insert_duplicate.sql (about) 1 CREATE TABLE IF NOT EXISTS indup_00( 2 `id` INT UNSIGNED, 3 `act_name` VARCHAR(20) NOT NULL, 4 `spu_id` VARCHAR(30) NOT NULL, 5 `uv` BIGINT NOT NULL, 6 `update_time` date default '2020-10-10' COMMENT 'lastest time', 7 unique key idx_act_name_spu_id (act_name,spu_id) 8 ); 9 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'); 10 select * from indup_00; 11 12 -- insert unique index duplicate data part,update value() and insert 13 insert into indup_00 values (4,'shenzheng','004',4,'2021-05-28'),(5,'beijing','010',5,'2022-10-23') on duplicate key update `act_name`=VALUES(`act_name`), `spu_id`=VALUES(`spu_id`), `uv`=VALUES(`uv`); 14 select * from indup_00; 15 -- insert unique index duplicate data all,update 16 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`); 17 select * from indup_00; 18 -- insert unique index duplicate data 19 insert into indup_00 values (8,'shanghai','002',21,'1999-09-23') on duplicate key update `act_name`=NULL; 20 select * from indup_00; 21 -- insert no duplicate data ,insert new data success 22 insert into indup_00 values (9,'shanxi','005',4,'2022-10-08'),(10,'shandong','006',6,'2022-11-22') on duplicate key update `act_name`='Hongkong'; 23 select * from indup_00; 24 insert into indup_00 values (10,'xinjiang','008',7,NULL),(11,'hainan','009',8,NULL) on duplicate key update `act_name`='Hongkong'; 25 select * from indup_00; 26 27 CREATE TABLE IF NOT EXISTS indup_01( 28 `id` INT UNSIGNED, 29 `act_name` VARCHAR(20) NOT NULL, 30 `spu_id` VARCHAR(30) NOT NULL, 31 `uv` BIGINT NOT NULL, 32 `update_time` date default '2020-10-10' COMMENT 'lastest time', 33 PRIMARY KEY ( `id` ), 34 unique key idx_act_name_spu_id (act_name,spu_id) 35 ); 36 insert into indup_01 values (1,'beijing','001',1,'2021-01-03'),(2,'shanghai','002',2,'2022-09-23'),(3,'guangzhou','003',3,'2022-09-23'); 37 select * from indup_01; 38 39 -- insert unique index duplicate data part,update value() and insert 40 insert into indup_01 values (4,'shenzheng','004',4,'2021-05-28'),(5,'beijing','010',5,'2022-10-23') on duplicate key update `act_name`=VALUES(`act_name`), `spu_id`=VALUES(`spu_id`), `uv`=VALUES(`uv`); 41 select * from indup_01; 42 -- insert unique index duplicate data all,update 43 insert into indup_01 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`); 44 select * from indup_01; 45 -- insert unique index duplicate data 46 insert into indup_01 values (8,'shanghai','002',21,'1999-09-23') on duplicate key update `act_name`=NULL; 47 select * from indup_01; 48 -- insert no duplicate data ,insert new data success 49 insert into indup_01 values (9,'shanxi','005',4,'2022-10-08'),(10,'shandong','006',6,'2022-11-22') on duplicate key update `act_name`='Hongkong'; 50 select * from indup_01; 51 insert into indup_01 values (10,'xinjiang','008',7,NULL),(11,'hainan','009',8,NULL) on duplicate key update `act_name`='Hongkong'; 52 select * from indup_01; 53 54 CREATE TABLE IF NOT EXISTS indup_02( 55 col1 INT , 56 col2 VARCHAR(20) NOT NULL, 57 col3 VARCHAR(30) NOT NULL, 58 col4 BIGINT default 30, 59 PRIMARY KEY ( col1 ) 60 ); 61 insert into indup_02 values (1,'apple','left',NULL),(2,'bear','right',1000); 62 select * from indup_02; 63 --insert primary key duplicate data,update col=expression 64 insert into indup_02 select 1,'banana','lower',NULL on duplicate key update col1=col1*10; 65 select * from indup_02; 66 --insert primary key duplicate data part,update and insert 67 insert into indup_02(col1,col2,col3) values(2,'wechat','tower'),(3,'paper','up') on duplicate key update col1=col1+20,col3=values(col3); 68 select * from indup_02; 69 --insert primary key duplicate data,after update data pk conflict old data 70 insert into indup_02 values(3,'aaa','bbb',30) on duplicate key update col1=col1+7; 71 select * from indup_02; 72 --insert primary key duplicate data, update data pk conflict other insert data 73 insert into indup_02 values(3,'aaa','bbb',30),(30,'abc','abc',10),(11,'a1','b1',300) on duplicate key update col1=col1*10,col4=0; 74 select * from indup_02; 75 --insert into select from table duplicate update,update col=function(col) col=constant 76 create table indup_tmp(col1 int,col2 varchar(20),col3 varchar(20)); 77 insert into indup_tmp values (1,'apple','left'),(2,'bear','right'),(3,'paper','up'),(10,'wine','down'),(300,'box','high'); 78 insert into indup_02(col1,col2,col3) select col1,col2,col3 from indup_tmp on duplicate key update indup_02.col3=left(indup_02.col3,2),col2='wow'; 79 select * from indup_02; 80 delete from indup_02; 81 select * from indup_02; 82 83 --insert primary key no duplicate data 84 insert into indup_02(col1,col2,col3) values(6,'app','uper'),(7,'light','') on duplicate key update col2=''; 85 select * from indup_02; 86 87 CREATE TABLE IF NOT EXISTS indup_03( 88 col1 varchar(25) , 89 col2 VARCHAR(20) NOT NULL, 90 col3 VARCHAR(30) , 91 col4 BIGINT default 30, 92 PRIMARY KEY (col1) 93 ); 94 insert into indup_03 values ('1','apple','left',NULL),('2','bear','right',1000); 95 -- insert primary key duplicate data and new data 96 insert into indup_03(col1,col2,col3) values(3,'paper','up'),('2','bear','right',1000),('1','sofa','high',NULL) on duplicate key update col2=values(col2),col3=values(col3); 97 select * from indup_03; 98 -- insert and update not null col 99 insert into indup_03(col1,col2,col3) values(4,NULL,NULL) on duplicate key update col2=values(col2),col3=values(col3); 100 insert into indup_03(col1,col2,col3) values(3,NULL,NULL) on duplicate key update col2=values(col2),col3=values(col3); 101 select * from indup_03; 102 --update pk constraint 103 insert into indup_03(col1,col2,col3) values(2,'bear','left') on duplicate key update col1=1; 104 -- update null/''/constant/expression 105 insert into indup_03(col1,col2,col3) values(1,'apple','') on duplicate key update col3='constant'; 106 select * from indup_03; 107 insert into indup_03(col1,col2,col3) values(1,'apple','uuuu') on duplicate key update col3=NULL; 108 select * from indup_03; 109 insert into indup_03(col1,col2,col3) values(1,'apple','uuuu') on duplicate key update col3=''; 110 select * from indup_03; 111 insert into indup_03(col1,col2,col3) values(1,'apple','uuuu') on duplicate key update col1=2+3; 112 select * from indup_03; 113 114 CREATE TABLE IF NOT EXISTS indup_04( 115 `id` INT, 116 `act_name` VARCHAR(20) NOT NULL, 117 `spu_id` VARCHAR(30) NOT NULL, 118 `uv` BIGINT NOT NULL, 119 `update_time` date default '2020-10-10' COMMENT 'lastest time', 120 PRIMARY KEY ( `id`, `act_name`) 121 ); 122 insert into indup_04 values (1,'beijing','001',1,'2021-01-03'),(2,'shanghai','002',2,'2022-09-23'),(3,'guangzhou','003',3,'2022-09-23'); 123 select * from indup_04; 124 125 -- insert comprimary key duplicate data part,update value() and insert 126 insert into indup_04 values (4,'shenzheng','004',4,'2021-05-28'),(1,'beijing','010',5,'2022-10-23') on duplicate key update `act_name`=VALUES(`act_name`), `spu_id`=VALUES(`spu_id`), `uv`=VALUES(`uv`); 127 select * from indup_04; 128 -- insert comprimary key duplicate data all,update 129 insert into indup_04 values (2,'shanghai','002',21,'1999-09-23'),(3,'guangzhou','003',31,'1999-09-23') on duplicate key update `act_name`=VALUES(`act_name`), `spu_id`=VALUES(`spu_id`), `uv`=VALUES(`uv`); 130 select * from indup_04; 131 -- insert comprimary key duplicate data 132 insert into indup_04 values (2,'shanghai','002',21,'1999-09-23') on duplicate key update `act_name`=NULL; 133 select * from indup_04; 134 -- insert no duplicate data ,insert new data success 135 insert into indup_04 values (5,'shanxi','005',4,'2022-10-08'),(6,'shandong','006',6,'2022-11-22') on duplicate key update `act_name`='Hongkong'; 136 select * from indup_04; 137 insert into indup_04 values (10,'xinjiang','008',7,NULL),(11,'hainan','009',8,NULL) on duplicate key update `act_name`='Hongkong'; 138 select * from indup_04; 139 140 -- foreign key constraint 141 create table indup_fk1(col1 int primary key,col2 varchar(25),col3 tinyint); 142 create table indup_fk2(col1 int,col2 varchar(25),col3 tinyint primary key,constraint ck foreign key(col1) REFERENCES indup_fk1(col1) on delete RESTRICT on update RESTRICT); 143 insert into indup_fk1 values (2,'yellow',20),(10,'apple',50),(11,'opppo',51); 144 insert into indup_fk2 values(2,'score',1),(2,'student',4),(10,'goods',2); 145 insert into indup_fk2 values(10,'food',1)on duplicate key update col1=50; 146 insert into indup_fk2 values(50,'food',1)on duplicate key update col1=values(col1); 147 select * from indup_fk1; 148 select * from indup_fk2; 149 drop table indup_fk2; 150 drop table indup_fk1; 151 152 -- without pk and unique index 153 CREATE TABLE IF NOT EXISTS indup_05( 154 col1 INT , 155 col2 VARCHAR(20) NOT NULL, 156 col3 VARCHAR(30) NOT NULL, 157 col4 BIGINT default 30 158 ); 159 insert into indup_05 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; 160 insert into indup_05 values(22,'78','30',99) on duplicate key update col1=col1/2; 161 select * from indup_05; 162 163 -- loop update conflict 164 create table indup_06(col1 int primary key,col2 int); 165 insert into indup_06 values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10),(11,11),(12,12),(13,13),(14,14),(15,15),(16,16),(17,17),(18,18),(19,19),(20,20); 166 insert into indup_06 values(1,10),(2,20),(3,30),(4,40),(5,50),(6,60),(7,70),(8,80),(9,90),(10,100),(11,110),(12,120),(13,130),(14,140),(15,150),(16,160),(17,170),(18,180),(19,190),(20,200)on duplicate key update col1=col1+1; 167 truncate table indup_06; 168 insert into indup_06 values(1,1); 169 insert into indup_06 values(1,10),(2,20),(3,30),(4,40),(5,50),(6,60),(7,70),(8,80),(9,90),(10,100),(11,110),(12,120),(13,130),(14,140),(15,150),(16,160),(17,170),(18,180),(19,190),(20,200)on duplicate key update col1=col1+1; 170 insert into indup_06 values(1,10),(2,20),(3,30),(4,40),(5,50),(6,60),(7,70),(8,80),(9,90),(10,100),(11,110),(12,120),(13,130),(14,140),(15,150),(16,160),(17,170),(18,180),(19,190),(20,200)on duplicate key update col1=col1+1,col2=col2*10; 171 insert into indup_06 values(1,10),(2,20),(3,30),(4,40),(5,50),(6,60),(7,70),(8,80),(9,90),(10,100),(11,110),(12,120),(13,130),(14,140),(15,150),(16,160),(17,170),(18,180),(19,190),(20,200)on duplicate key update col1=col1+1,col2=col2/10; 172 173 --insert duplicate data to null table 174 CREATE TABLE IF NOT EXISTS indup_07( 175 col1 INT primary key, 176 col2 VARCHAR(20) NOT NULL, 177 col3 VARCHAR(30) NOT NULL, 178 col4 BIGINT default 30 179 ); 180 insert into indup_07 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; 181 select * from indup_07; 182 183 --update out of date range 184 insert into indup_07 values(24,'1','1',100) on duplicate key update col1=2147483649; 185 186 --prepare 187 prepare stmt1 from "insert into indup_07 values(?, '11', '33', 1)on duplicate key update col1=col1*10"; 188 set @a_var = 1; 189 execute stmt1 using @a_var; 190 select * from indup_07; 191 set @a_var = 23; 192 execute stmt1 using @a_var; 193 select * from indup_07; 194 deallocate prepare stmt1; 195 196 --escape 197 drop table if exists indup_08; 198 create table indup_08(a varchar, primary key (a)); 199 insert into indup_08 values ('matrixone\''), ('matrixone\''); 200 201 202 drop table if exists indup_09; 203 create table indup_09(a varchar, b varchar, primary key (a, b)); 204 insert into indup_09 values ('matrixone\'', 'mo-tester\''), ('matrixone\'', 'mo-tester\''); 205 206 drop table if exists indup_10; 207 create table indup_10(a varchar(256), b int); 208 insert into indup_10 (a, b) select "matrixone " || " some space " || result, 1 from generate_series (1, 500000)g; 209 drop table indup_10;