An SQL

SQL

SQL is a programming language designed for managing data in relational database management systems ....

 UPDATE statement changes the data of one or more records in a table

Table (database)

In relational databases and flat file databases, a table is a set of data elements that is organized using a model of vertical columns and horizontal rows. A table has a specified number of columns, but can have any number of rows...

. Either all the rows can be updated, or a subset may be chosen using a condition

Condition (SQL)

A relational database management system uses SQL conditions or expressions in WHERE clauses and in HAVING clauses to SELECT subsets of data.- Types of condition :...

.

The UPDATE statement has the following form:

UPDATE table_name SET column_name = value [, column_name = value ...] [WHERE condition]

For the UPDATE to be successful, the user must have data manipulation privileges (UPDATE privilege) on the table or column, the updated value must not conflict with all the applicable constraints (such as primary keys, unique indexes, CHECK constraints

Check Constraint

A check constraint is a condition that defines valid data when adding or updating an entry in a table of a relational database. A check constraint is applied to each row in the table. The constraint must be a predicate. It can refer to a single or multiple columns of the table...

, and NOT NULL

Null (SQL)

Null is a special marker used in Structured Query Language to indicate that a data value does not exist in the database. Introduced by the creator of the relational database model, E. F. Codd, SQL Null serves to fulfill the requirement that all true relational database management systems support...

 constraints).

In some databases, such as PostgreSQL

PostgreSQL

PostgreSQL, often simply Postgres, is an object-relational database management system available for many platforms including Linux, FreeBSD, Solaris, MS Windows and Mac OS X. It is released under the PostgreSQL License, which is an MIT-style license, and is thus free and open source software...

, when a FROM clause is present, what essentially happens is that the target table is joined to the tables mentioned in the fromlist, and each output row of the join represents an update operation for the target table. When using FROM you should ensure that the join produces at most one output row for each row to be modified. In other words, a target row shouldn't join to more than one row from the other table(s). If it does, then only one of the join rows will be used to update the target row, but which one will be used is not readily predictable.

Because of this indeterminacy, referencing other tables only within sub-selects is safer, though often harder to read and slower than using a join.

Examples

Set the value of column C1 in table T to 1, only in those rows where the value of column C2 is "a".

UPDATE T SET C1 = 1 WHERE C2 = 'a'

In table T, set the value of column C1 to 9 and the value of C3 to 4 for all rows for which the value of column C2 is "a".

UPDATE T SET C1 = 9, C3 = 4 WHERE C2 = 'a'

Increase value of column C1 by 1 if the value in column C2 is "a".

UPDATE T SET C1 = C1 + 1 WHERE C2 = 'a'

Prepend the value in column C1 with the string "text" if the value in column C2 is "a".

UPDATE T SET C1 = 'text' || C1 WHERE C2 = 'a'

Set the value of column C1 in table T1 to 2, only if the value of column C2 is found in the sub list of values in column C3 in table T2 having the column C4 equal to 0.

UPDATE T1
SET C1 = 2
WHERE C2 IN ( SELECT C3
FROM T2
WHERE C4 = 0)

You may also update multiple columns in a single update statement:

UPDATE T SET C1 = 1, C2 = 2

Complex conditions and JOINs are also possible:

UPDATE T SET A = 1 WHERE C1 = 1 AND C2 = 2

Some databases allow the non-standard use of the FROM clause:
UPDATE a
SET a.[updated_column] = updatevalue
FROM articles a
JOIN classification c
ON a.articleID = c.articleID
WHERE c.classID = 1


Or on Oracle systems (assuming there is an index on classification.articleID)
UPDATE
(
SELECT *
FROM articles
JOIN classification
ON articles.articleID = classification.articleID
WHERE classification.classID = 1
)
SET [updated_column] = updatevalue

Potential Issues

See Halloween Problem

Halloween Problem

In computing, the Halloween Problem refers to a phenomenon in databases in which an update operation causes a change in the physical location of a row, potentially allowing the row to be visited more than once during the operation...

. It is possible for certain kinds of UPDATE statements to become an infinite loop

Infinite loop

An infinite loop is a sequence of instructions in a computer program which loops endlessly, either due to the loop having no terminating condition, having one that can never be met, or one that causes the loop to start over...

 when the WHERE

Where (SQL)

A WHERE clause in SQL specifies that a SQL Data Manipulation Language statement should only affect rows that meet specified criteria. The criteria are expressed in the form of predicates...

 clause and one or more SET clauses may utilize an intertwined index

Index (database)

A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of slower writes and increased storage space...

.