SQL Update

     
    UPDATE table SET assignments
    [FROM fromlist]
    [WHERE condition]
    assignments::= assignment {, assignment}
    assignment::= field = expression
     

An assignment expression setting the value of a column may use the old values of this and other columns in the table. The fromlist is a list of table expressions, allowing columns from other tables to appear in the WHERE condition and the update expressions. This is similar to the list of tables that can be specified in the FROM clause of a SELECT statement. A condition is an expression that returns a value of type boolean. Only rows for which this expression returns true are updated.

The SQL UPDATE statement modifies the values of one or more fields in zero or more rows of the specified table. When a FROM clause is present, essentially the target table is joined to the tables listed in the fromlist. Each output row of the join represents an update operation for the target table. When using FROM the developer must ensure that the join produces at most one output row for each row to be modified. In other words, a target row must not 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.

The UPDATE statement will fail if the values provided violate uniqueness constraints (hash or unique tree indexes).

Note: eXtremeDB and, by extension, eXtremeSQL, do not enforce referential integrity. Take care NOT to update primary key values for which there are one or more referencing foreign keys.