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