The UPDATE statement allows you to modify column values in a database.

INSERT, UPDATE, and DELETE statements behave in an atomic manner. That is, if an insert, update, or delete of more than one row fails, then all insertions, updates, or deletes of previous rows by the same statement are rolled back.

In the SET clause of an UPDATE statement, you may specify a sub-query. This feature allows you to update information in a table based on information in another table or another part of the same table.

You may specify the keyword DEFAULT to set the value to the default value defined for the given column. If no default value is defined, Pervasive PSQL uses NULL or pseudo-null value if the column is nullable, otherwise it returns an error. See also, further information on DEFAULT found in INSERT.

The UPDATE statement can update only a single table at a time. UPDATE can relate to other tables via a subquery in the SET clause. This can be a correlated subquery that depends in part on the contents of the table being updated, or it can be a non-correlated subquery that depends only on another table.

The same logic is used to process pure SELECT statements and subqueries, so the subquery can consist of any valid SELECT statement. There are no special rules for subqueries.

If SELECT within an UPDATE returns no rows, then the UPDATE inserts NULL. If the given column(s) is/are not nullable, then the UPDATE fails. If select returns more than one row, then UPDATE fails.

An UPDATE statement does not allow the use of join tables in the statement. Instead, use a correlated subquery in the SET clause as follows:

For example, in the statement UPDATE t1 SET c1 = 1 FROM t1, t2 WHERE t1.c2 = t2.c2, the t1 immediately after UPDATE is the same instance of table t1 as the t1 after FROM. Therefore, the statement is identical to UPDATE t1 SET c1 = 1 FROM t2 WHERE t1.c2 = t2.c2.

If the update table occurs in the FROM clause multiple times, one occurrence must be identified as the same instance as the update table. The FROM clause reference that is identified as the same instance as the update table is the one that does not have a specified alias.

Therefore, the statement UPDATE t1 SET t1.c1 = 1 FROM t1 a, t1 b WHERE a.c2 = b.c2 is invalid because both instances of t1 in the FROM clause contain an alias. The following version is valid: UPDATE t1 SET t1.c1 = 1 FROM t1, t1 b WHERE t1.c2 = b.c2.

Two tables are created and rows are inserted. The first table, t5, is updated with a column value from the second table, t6, in each row where table t5 has the value 2 for column c1. Because there is more than one row in table t6 containing a value of 3 for column c2, the first UPDATE fails because more than one row is returned by the subquery. This result occurs even though the result value is the same in both cases. As shown in the second UPDATE, using the DISTINCT keyword in the subquery eliminates the duplicate results and allows the statement to succeed.

Two tables are created and a variety of valid syntax examples are demonstrated. Note the cases where UPDATE fails because the subquery returns more than one row. Also note that UPDATE succeeds and NULL is inserted if the subquery returns no rows (where NULL values are allowed).