1# 2018-04-17 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# Test cases for UPSERT 13 14set testdir [file dirname $argv0] 15source $testdir/tester.tcl 16set testprefix upsert2 17 18foreach {tn sql} { 19 1 { CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c UNIQUE) } 20 2 { CREATE TABLE t1(a INT PRIMARY KEY, b, c UNIQUE) } 21 3 { CREATE TABLE t1(a INT PRIMARY KEY, b, c UNIQUE) WITHOUT ROWID} 22} { 23 reset_db 24 execsql $sql 25 26 do_execsql_test 1.$tn.0 { 27 INSERT INTO t1 VALUES(1, NULL, 'one'); 28 INSERT INTO t1 VALUES(2, NULL, 'two'); 29 INSERT INTO t1 VALUES(3, NULL, 'three'); 30 } 31 32 do_execsql_test 1.$tn.1 { 33 INSERT INTO t1 VALUES(1, NULL, 'xyz') ON CONFLICT DO NOTHING; 34 SELECT * FROM t1; 35 } { 36 1 {} one 2 {} two 3 {} three 37 } 38 39 do_execsql_test 1.$tn.2 { 40 INSERT INTO t1 VALUES(4, NULL, 'two') ON CONFLICT DO NOTHING; 41 SELECT * FROM t1; 42 } { 43 1 {} one 2 {} two 3 {} three 44 } 45 46 do_execsql_test 1.$tn.3 { 47 INSERT INTO t1 VALUES(4, NULL, 'two') ON CONFLICT (c) DO UPDATE SET b = 1; 48 SELECT * FROM t1; 49 } { 50 1 {} one 2 1 two 3 {} three 51 } 52 53 do_execsql_test 1.$tn.4 { 54 INSERT INTO t1 VALUES(2, NULL, 'zero') ON CONFLICT (a) DO UPDATE SET b=2; 55 SELECT * FROM t1; 56 } {1 {} one 2 2 two 3 {} three} 57 58 do_catchsql_test 1.$tn.5 { 59 INSERT INTO t1 VALUES(2, NULL, 'zero') ON CONFLICT (a) 60 DO UPDATE SET c = 'one'; 61 } {1 {UNIQUE constraint failed: t1.c}} 62 63 do_execsql_test 1.$tn.6 { 64 SELECT * FROM t1; 65 } {1 {} one 2 2 two 3 {} three} 66} 67 68finish_test 69 70