github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/replace_statement/replace_statement.sql (about)

     1  
     2  -- no primary key or unique
     3  create table t1(
     4  id int,
     5  data varchar(64)
     6  );
     7  
     8  replace into t1 values (1, 'new_1');
     9  select * from t1;
    10  
    11  replace into t1 values (1, 'new_2');
    12  select * from t1;
    13  
    14  replace into t1 values (1, 'new_1');
    15  select * from t1;
    16  
    17  -- A single column of primary keys exists
    18  create table t2(
    19  id int,
    20  data varchar(64),
    21  primary key (id)
    22  );
    23  
    24  replace into t2 values (1, 'test_1');
    25  select * from t2;
    26  
    27  replace into t2 values (2, 'test_1');
    28  select * from t2;
    29  
    30  replace into t2 values (1, 'old_1');
    31  select * from t2;
    32  
    33  replace into t2 values (2, 'old_2');
    34  select * from t2;
    35  
    36  
    37  -- Multiple columns of primary keys exist
    38  create table t3(
    39  id int,
    40  data varchar(64),
    41  name varchar(64) default null,
    42  primary key (id, data)
    43  );
    44  
    45  replace into t3 values (1, 'test_3', '');
    46  select * from t3;
    47  
    48  replace into t3 values (1, 'old_3', 'name1');
    49  select * from t3;
    50  
    51  replace into t3 values (2, 'test_3', 'name2');
    52  select * from t3;
    53  
    54  replace into t3 values (1, 'test_3', 'replace_name_1');
    55  select * from t3;
    56  
    57  replace into t3 values (2, 'test_3', 'replace_name_2');
    58  select * from t3;
    59  
    60  
    61  -- A uniquely constrained primary key
    62  create table t4 (
    63  a int unique key,
    64  b varchar(64)
    65  );
    66  
    67  replace into t4 values (1, 'a');
    68  select * from t4;
    69  replace into t4 values (2, 'a');
    70  select * from t4;
    71  replace into t4 values (1, 'replace_name_1');
    72  select * from t4;
    73  replace into t4 values (2, 'replace_name_2');
    74  select * from t4;
    75  
    76  -- Multiple uniquely constrained primary key
    77  create table t5(
    78  a int,
    79  b int,
    80  c varchar(64),
    81  unique key(a, b)
    82  );
    83  
    84  replace into t5 values (1, 1, '');
    85  select * from t5;
    86  replace into t5 values (1, 1, 'replace');
    87  select * from t5;
    88  replace into t5 values (1, 2, '');
    89  select * from t5;
    90  
    91  -- clear table
    92  drop table if exists t1;
    93  drop table if exists t2;
    94  drop table if exists t3;
    95  drop table if exists t4;
    96  drop table if exists t5;