github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/dml/insert/on_duplicate_key.sql (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  delete from t1;
     7  insert into t1 values (1,1);
     8  insert into t1 values (1,2), (2,2) on duplicate key update b=values(b)+10;
     9  select * from t1;
    10  delete from t1;
    11  insert into t1 values (1,1);
    12  insert into t1 values (1,11), (2,22), (3,33) on duplicate key update a=a+1,b=100;
    13  select * from t1;
    14  delete from t1;
    15  insert into t1 values (1,1);
    16  insert into t1 values (1,2), (1,22) on duplicate key update b=b+10;
    17  select * from t1;
    18  delete from t1;
    19  insert into t1 values (1,1),(3,3);
    20  insert into t1 values (1,2),(2,22) on duplicate key update a=a+1;
    21  delete from t1;
    22  insert into t1 values (1,1),(3,3);
    23  insert into t1 values (1,2),(2,22),(3,33) on duplicate key update a=a+1;
    24  drop table t1;
    25  create table t1(a int, b int, c int, primary key(a, b));
    26  delete from t1;
    27  insert into t1 values (1,1,1);
    28  insert into t1 values (1,1,2), (2,2,2) on duplicate key update c=c+10;
    29  select * from t1;
    30  delete from t1;
    31  insert into t1 values (1,1,1);
    32  insert into t1 values (1,1,2), (2,2,2) on duplicate key update c=values(c)+10;
    33  select * from t1;
    34  drop table t1;
    35  create table t1(a int unique key, b int);
    36  delete from t1;
    37  insert into t1 values (1,1);
    38  insert into t1 values (1,2), (2,2) on duplicate key update b=b+10;
    39  select * from t1;
    40  delete from t1;
    41  insert into t1 values (1,1);
    42  insert into t1 values (1,2), (2,2) on duplicate key update b=values(b)+10;
    43  select * from t1;
    44  delete from t1;
    45  insert into t1 values (1,1);
    46  insert into t1 values (1,11), (2,22), (3,33) on duplicate key update a=a+1,b=100;
    47  select * from t1;
    48  delete from t1;
    49  insert into t1 values (1,1);
    50  insert into t1 values (1,2), (1,22) on duplicate key update b=b+10;
    51  select * from t1;
    52  delete from t1;
    53  insert into t1 values (1,1),(3,3);
    54  insert into t1 values (1,2),(2,22) on duplicate key update a=a+1;
    55  delete from t1;
    56  insert into t1 values (1,1),(3,3);
    57  insert into t1 values (1,2),(2,22),(3,33) on duplicate key update a=a+1;
    58  drop table t1;
    59  create table t1(a int, b int, c int, unique key(a, b));
    60  delete from t1;
    61  insert into t1 values (1,1,1);
    62  insert into t1 values (1,1,2), (2,2,2) on duplicate key update c=c+10;
    63  select * from t1;
    64  delete from t1;
    65  insert into t1 values (1,1,1);
    66  insert into t1 values (1,1,2), (2,2,2) on duplicate key update c=values(c)+10;
    67  select * from t1;
    68  drop table t1;
    69  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));
    70  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');
    71  select * from indup_00 order by id;
    72  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`);
    73  select * from indup_00 order by id;
    74  drop table indup_00;
    75  CREATE TABLE IF NOT EXISTS indup(
    76  col1 INT primary key,
    77  col2 VARCHAR(20) NOT NULL,
    78  col3 VARCHAR(30) NOT NULL,
    79  col4 BIGINT default 30
    80  );
    81  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;
    82  select * from indup;
    83  insert into indup values(24,'1','1',100) on duplicate key update col1=2147483649;
    84  select * from indup;
    85  drop table indup;
    86  create table t1(a int primary key, b int, c int);
    87  insert into t1 values (1,1,1),(2,2,2);
    88  insert into t1 values (1,9,1),(11,8,2) on duplicate key update a=a+10, c=10;
    89  select * from t1 order by a;
    90  
    91  drop table if exists t1;
    92  create table t1(a int primary key, b int unique key);
    93  insert into t1 values (1,1),(2,2),(3,3);
    94  insert into t1 values (1,20) on duplicate key update b = b + 1;
    95  insert into t1 values (20,1) on duplicate key update a = a + 1;
    96  delete from t1;
    97  insert into t1 values (1,1),(3,2);
    98  insert into t1 values (1,2) on duplicate key update a = 10;
    99  delete from t1;
   100  insert into t1 values (1,1),(3,2);
   101  insert into t1 values (1,2) on duplicate key update a = a+2;