000001 hash-threshold 8 000002 000003 statement ok 000004 CREATE TABLE t1( x INTEGER, y VARCHAR(8) ) 000005 000006 statement ok 000007 INSERT INTO t1 VALUES(1,'true') 000008 000009 statement ok 000010 INSERT INTO t1 VALUES(0,'false') 000011 000012 statement ok 000013 INSERT INTO t1 VALUES(NULL,'NULL') 000014 000015 statement ok 000016 CREATE INDEX t1i1 ON t1(x) 000017 000018 000019 # EVIDENCE-OF: R-38515-45264 An UPDATE statement is used to modify a 000020 # subset of the values stored in zero or more rows of the database table 000021 # identified by the qualified-table-name specified as part of the UPDATE 000022 # statement. 000023 000024 statement ok 000025 UPDATE t1 SET x=1 WHERE x>0 000026 000027 statement ok 000028 UPDATE t1 SET x=2 WHERE x>0 000029 000030 statement ok 000031 UPDATE t1 SET y='true' WHERE x>0 000032 000033 statement ok 000034 UPDATE t1 SET y='unknown' WHERE x>0 000035 000036 statement error 000037 UPDATE t1 SET z='foo' 000038 000039 statement error 000040 UPDATE t1 SET z='foo' WHERE x>0 000041 000042 # TBD-EVIDENCE-OF: R-55869-30521 If the UPDATE statement does not have a 000043 # WHERE clause, all rows in the table are modified by the UPDATE. 000044 000045 statement ok 000046 UPDATE t1 SET x=3 000047 000048 query I rowsort 000049 SELECT count(*) FROM t1 WHERE x=3 000050 ---- 000051 3 000052 000053 # EVIDENCE-OF: R-58095-46013 Otherwise, the UPDATE affects only those 000054 # rows for which the WHERE clause boolean expression is true. 000055 000056 statement ok 000057 UPDATE t1 SET x=1 WHERE y='unknown' 000058 000059 query I rowsort 000060 SELECT count(*) FROM t1 WHERE x=1 000061 ---- 000062 1 000063 000064 # EVIDENCE-OF: R-58129-20729 It is not an error if the WHERE clause does 000065 # not evaluate to true for any row in the table - this just means that 000066 # the UPDATE statement affects zero rows. 000067 000068 statement ok 000069 UPDATE t1 SET x=1 WHERE y='foo' 000070 000071 # EVIDENCE-OF: R-40598-36595 For each affected row, the named columns 000072 # are set to the values found by evaluating the corresponding scalar 000073 # expressions. 000074 000075 statement ok 000076 UPDATE t1 SET x=3+1 000077 000078 query I rowsort 000079 SELECT count(*) FROM t1 WHERE x=4 000080 ---- 000081 3 000082 000083 # EVIDENCE-OF: R-34751-18293 If a single column-name appears more than 000084 # once in the list of assignment expressions, all but the rightmost 000085 # occurrence is ignored. 000086 000087 skipif mssql 000088 statement ok 000089 UPDATE t1 SET x=3, x=4, x=5 000090 000091 skipif mssql 000092 query I rowsort 000093 SELECT count(*) FROM t1 WHERE x=3 000094 ---- 000095 0 000096 000097 skipif mssql 000098 query I rowsort 000099 SELECT count(*) FROM t1 WHERE x=4 000100 ---- 000101 0 000102 000103 skipif mssql 000104 query I rowsort 000105 SELECT count(*) FROM t1 WHERE x=5 000106 ---- 000107 3 000108 000109 # EVIDENCE-OF: R-40472-60438 Columns that do not appear in the list of 000110 # assignments are left unmodified. 000111 000112 query I rowsort 000113 SELECT count(*) FROM t1 WHERE y='unknown' 000114 ---- 000115 1 000116 000117 statement ok 000118 UPDATE t1 SET x=2 000119 000120 query I rowsort 000121 SELECT count(*) FROM t1 WHERE y='unknown' 000122 ---- 000123 1 000124 000125 # EVIDENCE-OF: R-36239-04077 The scalar expressions may refer to columns 000126 # of the row being updated. 000127 000128 # EVIDENCE-OF: R-04558-24451 In this case all scalar expressions are 000129 # evaluated before any assignments are made. 000130 000131 statement ok 000132 UPDATE t1 SET x=x+2 000133 000134 query I rowsort 000135 SELECT count(*) FROM t1 WHERE x=4 000136 ---- 000137 3 000138 000139 # TBD-EVIDENCE-OF: R-12619-24112 The optional conflict-clause allows the 000140 # user to nominate a specific constraint conflict resolution algorithm 000141 # to use during this one UPDATE command. 000142 000143 # TBD-EVIDENCE-OF: R-12123-54095 The table-name specified as part of an 000144 # UPDATE statement within a trigger body must be unqualified. 000145 000146 # TBD-EVIDENCE-OF: R-09690-36749 In other words, the database-name. prefix 000147 # on the table name of the UPDATE is not allowed within triggers. 000148 000149 # TBD-EVIDENCE-OF: R-06085-13761 Unless the table to which the trigger is 000150 # attached is in the TEMP database, the table being updated by the 000151 # trigger program must reside in the same database as it. 000152 000153 # TBD-EVIDENCE-OF: R-29512-54644 If the table to which the trigger is 000154 # attached is in the TEMP database, then the unqualified name of the 000155 # table being updated is resolved in the same way as it is for a 000156 # top-level statement (by searching first the TEMP database, then the 000157 # main database, then any other databases in the order they were 000158 # attached). 000159 000160 # TBD-EVIDENCE-OF: R-19619-42762 The INDEXED BY and NOT INDEXED clauses are 000161 # not allowed on UPDATE statements within triggers. 000162 000163 # TBD-EVIDENCE-OF: R-57359-59558 The LIMIT and ORDER BY clauses for UPDATE 000164 # are unsupported within triggers, regardless of the compilation options 000165 # used to build SQLite. 000166 000167 # TBD-EVIDENCE-OF: R-59581-44104 If SQLite is built with the 000168 # SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option then the syntax 000169 # of the UPDATE statement is extended with optional ORDER BY and LIMIT 000170 # clauses 000171 000172 # TBD-EVIDENCE-OF: R-58862-44169 If an UPDATE statement has a LIMIT clause, 000173 # the maximum number of rows that will be updated is found by evaluating 000174 # the accompanying expression and casting it to an integer value. 000175 000176 # TBD-EVIDENCE-OF: R-63582-45120 A negative value is interpreted as "no 000177 # limit". 000178 000179 # TBD-EVIDENCE-OF: R-18628-11938 If the LIMIT expression evaluates to 000180 # non-negative value N and the UPDATE statement has an ORDER BY clause, 000181 # then all rows that would be updated in the absence of the LIMIT clause 000182 # are sorted according to the ORDER BY and the first N updated. 000183 000184 # TBD-EVIDENCE-OF: R-30955-38324 If the UPDATE statement also has an OFFSET 000185 # clause, then it is similarly evaluated and cast to an integer value. 000186 # If the OFFSET expression evaluates to a non-negative value M, then the 000187 # first M rows are skipped and the following N rows updated instead. 000188 000189 # TBD-EVIDENCE-OF: R-19486-35828 If the UPDATE statement has no ORDER BY 000190 # clause, then all rows that would be updated in the absence of the 000191 # LIMIT clause are assembled in an arbitrary order before applying the 000192 # LIMIT and OFFSET clauses to determine which are actually updated. 000193 000194 # TBD-EVIDENCE-OF: R-10927-26133 The ORDER BY clause on an UPDATE statement 000195 # is used only to determine which rows fall within the LIMIT. The order 000196 # in which rows are modified is arbitrary and is not influenced by the 000197 # ORDER BY clause.