~ubuntu-branches/ubuntu/wily/sqlite3/wily

onfocus="entersearch()" onblur="leavesearch()" style="width:24ex;padding:1px 1ex; border:solid white 1px; font-size:0.9em ; font-style:italic;color:#044a64;" value="Search SQLite Docs...">

112

113

</form>

114

</div>

115

</table>

116

</div></div></div></div>

117

</td></tr></table>

118

119

120

<h1 align="center">SQL As Understood By SQLite</h1><p><a href="lang.html">[Top]</a></p><h2>UPDATE</h2><h4><a href="syntaxdiagrams.html#update-stmt">update-stmt:</a></h4><blockquote> <img alt="syntax diagram update-stmt" src="images/syntax/update-stmt.gif"></img> </blockquote>

121

<h4><a href="syntaxdiagrams.html#qualified-table-name">qualified-table-name:</a></h4><blockquote> <img alt="syntax diagram qualified-table-name" src="images/syntax/qualified-table-name.gif"></img> </blockquote>

122

123

124

<p>An UPDATE statement is used to modify a subset of the values stored in

125

zero or more rows of the database table identified by the

126

<i>qualified-table-name</i> specified as part of the UPDATE statement.

127

128

<p>If the UPDATE statement does not have a WHERE clause, all rows in the

129

table are modified by the UPDATE. Otherwise, the UPDATE affects only those

130

rows for which the result of evaluating the WHERE clause expression as a

131

<a href="lang_expr.html#booleanexpr">boolean expression is true</a>. It is not an error if the

132

WHERE clause does not evaluate to true for any row in the table - this just

133

means that the UPDATE statement affects zero rows.

134

135

<p>The modifications made to each row affected by an UPDATE statement are

136

determined by the list of assignments following the SET keyword. Each

137

assignment specifies a column name to the left of the equals sign and a

138

scalar expression to the right. For each affected row, the named columns

139

are set to the values found by evaluating the corresponding scalar

140

expressions. If a single column-name appears more than once in the list of

141

assignment expressions, all but the rightmost occurrence is ignored. Columns

142

that do not appear in the list of assignments are left unmodified. The scalar

143

expressions may refer to columns of the row being updated. In this case all

144

scalar expressions are evaluated before any assignments are made.

145

146

<p>The optional conflict-clause allows the user to nominate a specific

147

constraint conflict resolution algorithm to use during this one UPDATE command.

148

Refer to the section entitled <a href="lang_conflict.html">ON CONFLICT</a> for additional information.

149

150

<h3>Restrictions on UPDATE Statements Within CREATE TRIGGER</h3>

151

152

<p>The following additional syntax restrictions apply to UPDATE statements that

153

occur within the body of a <a href="lang_createtrigger.html">CREATE TRIGGER</a> statement.

154

155

<ul>

156

<li><p>The <i>table-name</i> specified as part of an UPDATE statement within

157

a trigger body must be unqualified. In other words, the

158

<i>database-name</i><b>.</b> prefix on the table name of the UPDATE is

159

not allowed within triggers. Unless the table to which the trigger

160

is attached is in the TEMP database, the table being updated by the

161

trigger program must reside in the same database as it. If the table

162

to which the trigger is attached is in the TEMP database, then the

163

unqualified name of the table being updated is resolved in the same way

164

as it is for a top-level statement (by searching first the TEMP database,

165

then the main database, then any other databases in the order they were

166

attached).

167

168

<li><p>The INDEXED BY and NOT INDEXED clauses are not allowed on UPDATE

169

statements within triggers.</p>

170

171

<li><p>The LIMIT and ORDER BY clauses for UPDATE are unsupported within

172

triggers, regardless of the compilation options used to build SQLite.

173

</ul>

174

175

<h3>Optional LIMIT and ORDER BY Clauses</h3>

176

177

<p>If SQLite is built with the <a href="compile.html#enable_update_delete_limit">SQLITE_ENABLE_UPDATE_DELETE_LIMIT</a>

178

compile-time option then the syntax of the UPDATE statement is extended

179

with optional ORDER BY and LIMIT clauses as follows:</p>

180

181

<h4><a href="syntaxdiagrams.html#update-stmt-limited">update-stmt-limited:</a></h4><blockquote> <img alt="syntax diagram update-stmt-limited" src="images/syntax/update-stmt-limited.gif"></img> </blockquote>

182

183

184

<p>If an UPDATE statement has a LIMIT clause, the maximum number of rows that

185

will be updated is found by evaluating the accompanying expression and casting

186

it to an integer value. A negative value is interpreted as "no limit".

187

188

<p>If the LIMIT expression evaluates to non-negative value <i>N</i> and the

189

UPDATE statement has an ORDER BY clause, then all rows that would be updated in

190

the absence of the LIMIT clause are sorted according to the ORDER BY and the

191

first <i>N</i> updated. If the UPDATE statement also has an OFFSET clause,

192

then it is similarly evaluated and cast to an integer value. If the OFFSET

193

expression evaluates to a non-negative value <i>M</i>, then the first <i>M</i>

194

rows are skipped and the following <i>N</i> rows updated instead.

195

196

<p>If the UPDATE statement has no ORDER BY clause, then all rows that

197

would be updated in the absence of the LIMIT clause are assembled in an

198

arbitrary order before applying the LIMIT and OFFSET clauses to determine

199

which are actually updated.

200

201

<p>The ORDER BY clause on an UPDATE statement is used only to determine which

202

rows fall within the LIMIT. The order in which rows are modified is arbitrary

203

and is not influenced by the ORDER BY clause.

204

205