Command UPDATE - SQL Foundation

Updates one or more records in a table. END

UPDATE <cTableName>  
       SET <cFieldName> := <Expression>|<SubSelectExpression> [, ...] 
       | FROM (aData|oData)
       WHERE <lExpression>
       [VIA (<coSession>)]
<cTableName> specifies the name of the table which contains the records to be updated.
The name of a column in the table specified.

The table name cannot be used as a prefix in <cFieldName>. For example, UPDATE tab SET tab.col=1 is invalid.

:= <Expression>
An expression returning the values to assign to the fields in the column. The expression can access the old values in all the columns in the table, including the old value of column being updated. See FieldGetOld() for further information.
:= <SubSelectExpression>
<SubSelectExpression> specifies an SQL SELECT statement whose result set contains the values to use for updating the table. Any type of SQL SELECT statement can be used with this parameter, including statements using joins and filters. However, the INTO and EVAL clauses can not be used in subselects.
WHERE <lExpression>
Specifies one or more filter conditions the records must meet in order to be updated.
VIA (<oSession>)
<oSession> optionally specifies the Session context for the operation. oSession is an object of DacSession() or any derived class and must be specified as an object expression in parentheses. If <oSession> is not specified, the SQL UPDATE statement is executed by the local SQL engine. If a VIA clause is specified in an SQL statement on the other hand, the statement executes in the context defined by the session which in most cases involves a remote backend.

UPDATE changes the values of the specified columns in all rows that meet the filter condition. Only the columns to be modified must be listed in the SET clause. Columns not explicitly modified retain their previous values.

There are two ways to modify a table using information contained in other tables in the database: by using sub-selects, or by specifying additional tables in the FROM clause. Which technique is more appropriate depends on the specific circumstances.

Unlike REPLACE, the SQL UPDATE command can be used to update multiple records at once. If the database is open in shared mode, each record must be locked and unlocked in turn for this operation, which may introduce a substantial delay. For optimal performance it is therefore recommended to open the table in exclusive mode when updating multiple records using SQL UPDATE. Alternatively, the FLock() function can be used to lock the table as a whole before performing the update.


If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.