An UPDATE statement is used to modify a subset of the values stored in zero or more rows of the database table identified by the qualified-table-name specified as part of the UPDATE statement.
If the UPDATE statement does not have a WHERE clause, all rows in the table are modified by the UPDATE. Otherwise, the UPDATE affects only those rows for which the WHERE clause boolean expression is true. It is not an error if the WHERE clause does not evaluate to true for any row in the table - this just means that the UPDATE statement affects zero rows.
The modifications made to each row affected by an UPDATE statement are determined by the list of assignments following the SET keyword. Each assignment specifies a column-name to the left of the equals sign and a scalar expression to the right. For each affected row, the named columns are set to the values found by evaluating the corresponding scalar expressions. If a single column-name appears more than once in the list of assignment expressions, all but the rightmost occurrence is ignored. Columns that do not appear in the list of assignments are left unmodified. The scalar expressions may refer to columns of the row being updated. In this case all scalar expressions are evaluated before any assignments are made.
Beginning in SQLite version 3.15.0 (2016-10-14), an assignment in the SET clause can be a parenthesized list of column names on the left and a row value of the same size on the right.
The optional "OR action" conflict clause that follows the UPDATE keyword allows the user to nominate a specific constraint conflict resolution algorithm to use during this one UPDATE command. Refer to the section entitled ON CONFLICT for additional information.
The following additional syntax restrictions apply to UPDATE statements that occur within the body of a CREATE TRIGGER statement.
The table-name specified as part of an UPDATE statement within a trigger body must be unqualified. In other words, the schema-name. prefix on the table name of the UPDATE is not allowed within triggers. Unless the table to which the trigger is attached is in the TEMP database, the table being updated by the trigger program must reside in the same database as it. If the table to which the trigger is attached is in the TEMP database, then the unqualified name of the table being updated is resolved in the same way as it is for a top-level statement (by searching first the TEMP database, then the main database, then any other databases in the order they were attached).
The INDEXED BY and NOT INDEXED clauses are not allowed on UPDATE statements within triggers.
The LIMIT and ORDER BY clauses for UPDATE are unsupported within triggers, regardless of the compilation options used to build SQLite.
The UPDATE-FROM idea is an extension to SQL that allows an UPDATE statement to be driven by other tables in the database. The "target" table is the specific table that is being updated. With UPDATE-FROM you can join the target table against other tables in the database in order to help compute which rows need updating and what the new values should be on those rows. UPDATE-FROM is supported beginning in SQLite version 3.33.0 (2020-08-14).
Other relation database engines also implement UPDATE-FROM, but because the construct is not part of the SQL standards, each product implements UPDATE-FROM differently. The SQLite implementation strives to be compatible with PostgreSQL. The SQL Server and MySQL implementations of the same idea work a little differently.
As an example of how UPDATE-FROM can be useful, suppose you have a point-of-sale application that accumulates purchases in the SALES table. At the end of the day, you want to adjust the INVENTORY table according to the daily sales. To do this, you can run an UPDATE against the INVENTORY table that adjusts the quantity by the aggregated sales for the day. The statement would look like this:
UPDATE inventory SET quantity = quantity - daily.amt FROM (SELECT sum(quantity) AS amt, itemId FROM sales GROUP BY 2) AS daily WHERE inventory.itemId = daily.itemId;
The subquery in the FROM clause computes the amount by which the inventory should be reduced for each itemId. That subquery is joined against the inventory table and the quantity of each affected inventory row is reduced by the appropriate amount.
The target table is not included in the FROM clause, unless the intent is to do a self-join against the target table. In the event of a self-join, the table in the FROM clause must be aliased to a different name than the target table.
If the join between the target table and the FROM clause results in multiple output rows for the same target table row, then only one of those output rows is used for updating the target table. The output row selected is arbitrary and might change from one release of SQLite to the next, or from one run to the next.
SQL Server also supports UPDATE FROM, but in SQL Server the target table must be included in the FROM clause. In other words, the target table is named twice in the statement. With SQL Server, the inventory adjustment statement demonstrated above would be written like this:
UPDATE inventory
SET quantity = quantity - daily.amt
FROM inventory,
(SELECT sum(quantity) AS amt, itemId FROM sales GROUP BY 2) AS daily
WHERE inventory.itemId = daily.itemId;
MySQL supports the UPDATE FROM idea, but it does so without using a FROM clause. Instead, the complete join specification is given in between the UPDATE and SET keywords. The equivalent MySQL statement would be like this:
UPDATE inventory JOIN
(SELECT sum(quantity) AS amt, itemId FROM sales GROUP BY 2) AS daily
USING( itemId )
SET inventory.quantity = inventory.quantity - daily.amt;
The MySQL UPDATE statement does not have just one target table like other systems. Any of the tables that participate in the join can be modified in the SET clause. The MySQL UPDATE syntax allows you to update multiple tables at once!
If SQLite is built with the SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option then the syntax of the UPDATE statement is extended with optional ORDER BY and LIMIT clauses as follows:
If an UPDATE statement has a LIMIT clause, the maximum number of rows that will be updated is found by evaluating the accompanying expression and casting it to an integer value. A negative value is interpreted as "no limit".
If the LIMIT expression evaluates to non-negative value N and the UPDATE statement has an ORDER BY clause, then all rows that would be updated in the absence of the LIMIT clause are sorted according to the ORDER BY and the first N updated. If the UPDATE statement also has an OFFSET clause, then it is similarly evaluated and cast to an integer value. If the OFFSET expression evaluates to a non-negative value M, then the first M rows are skipped and the following N rows updated instead.
If the UPDATE statement has no ORDER BY clause, then all rows that would be updated in the absence of the LIMIT clause are assembled in an arbitrary order before applying the LIMIT and OFFSET clauses to determine which are actually updated.
The ORDER BY clause on an UPDATE statement is used only to determine which rows fall within the LIMIT. The order in which rows are modified is arbitrary and is not influenced by the ORDER BY clause.
SQLite is in the Public Domain.
https://sqlite.org/lang_update.html