gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/upfrom1.tcl (about)

     1  # 2020 April 22
     2  #
     3  # The author disclaims copyright to this source code.  In place of
     4  # a legal notice, here is a blessing:
     5  #
     6  #    May you do good and not evil.
     7  #    May you find forgiveness for yourself and forgive others.
     8  #    May you share freely, never taking more than you give.
     9  #
    10  #***********************************************************************
    11  #
    12  
    13  source [file join [file dirname $argv0] pg_common.tcl]
    14  
    15  #=========================================================================
    16  
    17  start_test upfrom1 "2020 April 22"
    18  
    19  foreach {tn wo} {
    20    1 "WITHOUT ROWID"
    21    2 ""
    22  } {
    23  eval [string map [list %TN% $tn %WITHOUT_ROWID% $wo] {
    24  execsql_test 1.%TN%.0 {
    25    DROP TABLE IF EXISTS t2;
    26    CREATE TABLE t2(a INTEGER PRIMARY KEY, b INTEGER, c INTEGER) %WITHOUT_ROWID%;
    27    INSERT INTO t2 VALUES(1, 2, 3);
    28    INSERT INTO t2 VALUES(4, 5, 6);
    29    INSERT INTO t2 VALUES(7, 8, 9);
    30  
    31    DROP TABLE IF EXISTS chng;
    32    CREATE TABLE chng(a INTEGER, b INTEGER, c INTEGER);
    33    INSERT INTO chng VALUES(1, 100, 1000);
    34    INSERT INTO chng VALUES(7, 700, 7000);
    35  }
    36  
    37  execsql_test 1.%TN%.1 {
    38    SELECT * FROM t2;
    39  }
    40  
    41  execsql_test 1.%TN%.2 {
    42    UPDATE t2 SET b = chng.b, c = chng.c FROM chng WHERE chng.a = t2.a;
    43    SELECT * FROM t2 ORDER BY a;
    44  }
    45  
    46  execsql_test 1.%TN%.3 {
    47    DELETE FROM t2;
    48    INSERT INTO t2 VALUES(1, 2, 3);
    49    INSERT INTO t2 VALUES(4, 5, 6);
    50    INSERT INTO t2 VALUES(7, 8, 9);
    51  }
    52  
    53  execsql_test 1.%TN%.4 {
    54    UPDATE t2 SET (b, c) = (SELECT b, c FROM chng WHERE a=t2.a) 
    55      WHERE a IN (SELECT a FROM chng);
    56    SELECT * FROM t2 ORDER BY a;
    57  }
    58  
    59  execsql_test 1.%TN%.5 {
    60    DROP TABLE IF EXISTS t3;
    61    CREATE TABLE t3(a INTEGER PRIMARY KEY, b INTEGER, c TEXT) %WITHOUT_ROWID%;
    62    INSERT INTO t3 VALUES(1, 1, 'one');
    63    INSERT INTO t3 VALUES(2, 2, 'two');
    64    INSERT INTO t3 VALUES(3, 3, 'three');
    65  
    66    DROP TABLE IF EXISTS t4;
    67    CREATE TABLE t4(x TEXT);
    68    INSERT INTO t4 VALUES('five');
    69  
    70    SELECT * FROM t3 ORDER BY a;
    71  }
    72  
    73  execsql_test 1.%TN%.6 {
    74    UPDATE t3 SET c=x FROM t4;
    75    SELECT * FROM t3 ORDER BY a;
    76  }
    77  }]}
    78  
    79  execsql_test 2.1 {
    80    DROP TABLE IF EXISTS t5;
    81    DROP TABLE IF EXISTS m1;
    82    DROP TABLE IF EXISTS m2;
    83    CREATE TABLE t5(a INTEGER PRIMARY KEY, b TEXT, c TEXT);
    84    CREATE TABLE m1(x INTEGER PRIMARY KEY, y TEXT);
    85    CREATE TABLE m2(u INTEGER PRIMARY KEY, v TEXT);
    86  
    87    INSERT INTO t5 VALUES(1, 'one', 'ONE');
    88    INSERT INTO t5 VALUES(2, 'two', 'TWO');
    89    INSERT INTO t5 VALUES(3, 'three', 'THREE');
    90    INSERT INTO t5 VALUES(4, 'four', 'FOUR');
    91  
    92    INSERT INTO m1 VALUES(1, 'i');
    93    INSERT INTO m1 VALUES(2, 'ii');
    94    INSERT INTO m1 VALUES(3, 'iii');
    95  
    96    INSERT INTO m2 VALUES(1, 'I');
    97    INSERT INTO m2 VALUES(3, 'II');
    98    INSERT INTO m2 VALUES(4, 'III');
    99  }
   100  
   101  execsql_test 2.2 {
   102    UPDATE t5 SET b=y, c=v FROM m1 LEFT JOIN m2 ON (x=u) WHERE x=a;
   103    SELECT * FROM t5 ORDER BY a;
   104  }
   105  
   106  errorsql_test 2.3.1 {
   107    UPDATE t5 SET b=1 FROM t5;
   108  }
   109  errorsql_test 2.3.2 {
   110    UPDATE t5 AS apples SET b=1 FROM t5 AS apples;
   111  }
   112  
   113  
   114  finish_test
   115