github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/dml/update/update_index.test (about)

     1  drop database if exists db1;
     2  create database db1;
     3  use db1;
     4  drop table if exists t1;
     5  create table t1(a int, b int, c int, unique key(a));
     6  insert into t1 values(1, 1, 1);
     7  insert into t1 values(2, 2, 2);
     8  insert into t1 values(3, 3, 3);
     9  insert into t1 values(4, 4, 4);
    10  insert into t1 values(5, 5, 5);
    11  insert into t1 values(null, 7, 7);
    12  update t1 set a = 6 where a = 1;
    13  insert into t1 values(1, 1, 1);
    14  update t1 set a = 7 where a is null;
    15  insert into t1 values(7, 7, 7);
    16  update t1 set a = null where a = 7;
    17  insert into t1 values(7, 7, 7);
    18  select * from t1;
    19  drop table if exists t1;
    20  create table t1(a int, b int, c int, unique key(a, b));
    21  insert into t1 values(1, 1, 1);
    22  insert into t1 values(2, 2, 2);
    23  insert into t1 values(3, 3, 3);
    24  insert into t1 values(4, 4, 4);
    25  insert into t1 values(5, 5, 5);
    26  
    27  insert into t1 values(null, 7, 7);
    28  update t1 set a = 6 where a = 1;
    29  insert into t1 values(1, 1, 1);
    30  update t1 set a = 7 where a is null;
    31  -- @pattern
    32  insert into t1 values(7, 7, 7);
    33  update t1 set a = null where a = 7;
    34  insert into t1 values(7, 7, 7);
    35  insert into t1 values(null, 8, 8);
    36  update t1 set a = 8, b = null where b = 8;
    37  insert into t1 values(8, 8, 8);
    38  select * from t1;
    39  
    40  drop table if exists t1;
    41  drop table if exists t2;
    42  create table t1(a int, b int, c int, unique key(a, b));
    43  create table t2(a int, b int, c int, unique key(a));
    44  insert into t1 values(1, 1, 1);
    45  insert into t1 values(2, 2, 2);
    46  insert into t1 values(3, 3, 3);
    47  insert into t1 values(4, 4, 4);
    48  insert into t1 values(5, 5, 5);
    49  insert into t1 values(null, 7, 7);
    50  insert into t2 values(1, 1, 1);
    51  insert into t2 values(2, 2, 2);
    52  insert into t2 values(3, 3, 3);
    53  insert into t2 values(4, 4, 4);
    54  insert into t2 values(5, 5, 5);
    55  insert into t2 values(null, 7, 7);
    56  update t1, t2 set t1.a = 6, t2.a = 6 where t1.a = 1 and t1.a = t2.a;
    57  insert into t1 values(1, 1, 1);
    58  insert into t2 values(1, 1, 1);
    59  update t1, t2 set t1.a = 7, t2.a = 7 where t1.a is null and t2.a is null;
    60  -- @pattern
    61  insert into t1 values(7, 7, 7);
    62  insert into t2 values(7, 7, 7);
    63  update t1, t2 set t1.a = null, t2.a = null where t1.a = 7 and t1.a = t2.a;
    64  insert into t1 values(7, 7, 7);
    65  insert into t1 values(null, 8, 8);
    66  insert into t2 values(7, 7, 7);
    67  insert into t2 values(null, 8, 8);
    68  update t1, t2 set t1.a = 8, t1.b = null, t2.a = 8, t2.b = null where t1.b = 8 and t1.b = t2.b;
    69  insert into t1 values(8, 8, 8);
    70  insert into t2 values(8, 8, 8);
    71  select * from t1;
    72  select * from t2;
    73  drop table if exists t1;
    74  create table t1(a int, b int, c int, unique key(a), primary key(c));
    75  insert into t1 values(1, 1, 1);
    76  insert into t1 values(2, 2, 2);
    77  insert into t1 values(3, 3, 3);
    78  insert into t1 values(4, 4, 4);
    79  insert into t1 values(5, 5, 5);
    80  insert into t1 values(null, 7, 7);
    81  update t1 set a = 6 where a = 1;
    82  update t1 set a = 7 where a is null;
    83  insert into t1 values(7, 7, 8);
    84  update t1 set a = null where a = 7;
    85  insert into t1 values(7, 7, 9);
    86  select * from t1;
    87  drop table if exists t1;
    88  create table t1(a int, b int, c int, unique key(a, b), primary key(b, c));
    89  insert into t1 values(1, 1, 1);
    90  insert into t1 values(2, 2, 2);
    91  insert into t1 values(3, 3, 3);
    92  insert into t1 values(4, 4, 4);
    93  insert into t1 values(5, 5, 5);
    94  insert into t1 values(null, 7, 7);
    95  update t1 set a = 6 where a = 1;
    96  update t1 set a = 7 where a is null;
    97  -- @pattern
    98  insert into t1 values(7, 7, 8);
    99  update t1 set a = null where a = 7;
   100  insert into t1 values(7, 7, 9);
   101  insert into t1 values(null, 8, 10);
   102  update t1 set a = 8, b = null where b = 8;
   103  insert into t1 values(8, 8, 11);
   104  select * from t1;
   105  drop table if exists t1;
   106  create table t1(a int unique, b int, c int);
   107  insert into t1 values(1, 1, 1);
   108  insert into t1 values(2, 2, 2);
   109  insert into t1 values(3, 3, 3);
   110  insert into t1 values(4, 4, 4);
   111  insert into t1 values(5, 5, 5);
   112  insert into t1 values(null, 7, 7);
   113  update t1 set a = 6 where a = 1;
   114  insert into t1 values(1, 1, 1);
   115  update t1 set a = 7 where a is null;
   116  insert into t1 values(7, 7, 7);
   117  update t1 set a = null where a = 7;
   118  insert into t1 values(7, 7, 7);
   119  select * from t1;
   120  drop table if exists t1;
   121  create table t1(a int unique key, b int, c int);
   122  insert into t1 values(1, 1, 1);
   123  insert into t1 values(2, 2, 2);
   124  insert into t1 values(3, 3, 3);
   125  insert into t1 values(4, 4, 4);
   126  insert into t1 values(5, 5, 5);
   127  insert into t1 values(null, 7, 7);
   128  update t1 set a = 6 where a = 1;
   129  insert into t1 values(1, 1, 1);
   130  update t1 set a = 7 where a is null;
   131  insert into t1 values(7, 7, 7);
   132  update t1 set a = null where a = 7;
   133  insert into t1 values(7, 7, 7);
   134  select * from t1;
   135  drop table t1;
   136  drop table if exists t2;
   137  create table t2(a int primary key, b int unique, c int);
   138  insert into t2 values(1, 1, 1);
   139  insert into t2 values(2, 2, 2);
   140  insert into t2 values(3, 3, 3);
   141  insert into t2 values(4, 4, 4);
   142  insert into t2 values(5, 5, 5);
   143  update t2 set a = 6 where b = 5;
   144  select * from t2;
   145  drop table t2;
   146  
   147  create table t3(col1 int primary key, col2 int);
   148  create unique index key1 on t3(col2,col1);
   149  insert into t3 values(1 , 100);
   150  insert into t3 values(2, 200);
   151  insert into t3 values(3 , 300);
   152  update t3 set col2 = 300 where col1 = 3;
   153  update t3 set col2 = 100 where col1 = 1;
   154  update t3 set col2 = 200 where col1 = 2;