Name

UPDATE — Modify existing rows in a table

Syntax

Common Usage

UPDATE database_name.table_name SET col5 = val5, col2 = val2 WHERE id = 42;

Description

The UPDATE command modifies one or more column values within existing table rows. The command starts out with a conflict resolution clause, followed by the name of the table that contains the rows we’re updating. The table name is followed by a list of column names and new values. The final WHERE clause determines which rows are updated. A WHERE condition that causes no rows to be updated is not considered an error. If no WHERE clause is given, every row in the table is updated.

Warning

An UPDATE command with no WHERE clause will update every row in a table.

The values that are used to update a row may be given as expressions. These expressions are evaluated within the context of the original row values. This allows the value expression to refer to the old row value. For example, to increment a column value by one, you might find SQL similar to UPDATE...SET col = col + 1 WHERE.... Columns and values can be given in any order, as long as they appear in pairs. Any column, including ROWID, may be updated. Any columns that do not appear in the UPDATE command remain unmodified. There is no way to return a column to its default value.

If the SQLite library has been compiled with the optional SQLITE_ENABLE_UPDATE_DELETE_LIMIT directive, an optional ORDER BY...LIMIT clause may be used to update a specific number of rows. See the SQLite website (http://www.sqlite.org/lang_update.html) for more details.

The optional conflict resolution clause found at the beginning of the UPDATE (or INSERT) command is a nonstandard extension that controls how SQLite reacts to a constraint violation. For example, if a column must be unique, any attempt to update the value of that column to a value that is already in use by another row would cause a constraint violation. The constraint resolution clause determines how this situation is resolved.

ROLLBACK

A ROLLBACK is immediately issued, rolling back any current transaction. An SQLITE_CONSTRAINT error is returned to the calling process. If no explicit transaction is currently in progress, the behavior will be identical to an ABORT.

ABORT

This is the default behavior. Changes caused by the current command are undone and SQLITE_CONSTRAINT is returned, but no ROLLBACK is issued. For example, if a constraint violation is encountered on the fourth of ten possible row updates, the first three rows will be reverted, but the current transaction will remain active.

FAIL

The command will fail and return SQLITE_CONSTRAINT, but any rows that have been previously modified will not be reverted. For example, if a constraint violation is encountered on the fourth of ten possible row updates, the first three modifications will be left in place and further processing will be cut short. The current transaction will not be committed or rolled back.

IGNORE

Any constraint violation error is ignored. The row update will not be processed, but no error is returned. For example, if a constraint violation is encountered on the fourth of ten possible rows, not only are the first three row modifications left in place, processing continues with the remaining rows.

REPLACE

The specific action taken by a REPLACE resolution depends on what type of constraint is violated.

If a UNIQUE constraint is violated, the existing rows that are causing the constraint violation will first be deleted, and then the UPDATE (or INSERT) is allowed to be processed. No error is returned. This allows the command to succeed, but may result in one or more rows being deleted. In this case, any delete triggers associated with this table will not fire unless recursive triggers are enabled. Currently, the update hook is not called for automatically deleted rows, nor is the change counter incremented. These two behaviors may change in a future version, however.

If a NOT NULL constraint is violated, the NULL is replaced by the default value for that column. If no default value has been defined, the ABORT resolution is used.

If a CHECK constraint is violated, the IGNORE resolution is used.

Any conflict resolution clause found in an UPDATE (or INSERT) command will override any conflict resolution clause found in a table definition.

See Also

INSERT, DELETE, CREATE TABLE