UPDATE SQL Command
Updates records in a single DBF file with new values.
SET Column_Name1 = eExpression1 [, Column_Name2 = eExpression2 ...]
[FROM Table_List_Item [[, ...] | [JOIN [ Table_List_Item]]]
WHERE FilterCondition1 [AND | OR FilterCondition2 ...]
Specifies a target DBF file to update. You can include multiple sources of data for the update operation in the FROM clause.
Specifies the columns in the table to update and their new values. If you omit the WHERE clause, every row in the table is updated with the same value.
You can include one subquery in the SET clause to specify an expression. If the subquery does not return any results, it returns NULL. For syntax and information about subqueries, see SELECT SQL Command topic.
Specifies one or more DBF files containing the data for the update operation.
The FROM clause has almost the same syntax as in the SQL SELECT command. The restrictions are:
- The target DBF file cannot be included in an OUTER join as a secondary table.
- It should be possible to evaluate all other JOIN operations of the SQL query before performing a JOIN operation on the target file.
Table_List_Item can be:
- Name of the DBF file you want to update data from.
JOIN provides the capability for specifying one or more secondary files. There is no limit on the number of tables or JOIN clauses per UPDATE statement.
- (Subquery) AS Subquery_Alias
A subquery specifies a SELECT statement within another SELECT statement. For more information about subqueries, see SELECT SQL Command topic.
Specifies one or more filter conditions that records must meet to be updated with new values. Filter conditions number is not limited.
Update field "order_amt" in table "orders.dbf" with zero value. Records must meet the following requirements: field "to_city" is not empty, and field "cust_id" contains NULL value:
Update the field with the results of another query:
Update the field with values from another DBF table. Comparison occur by values of fields "orditems.line_no" and "orders.ship_via" (converted into Integer value with VAL() function):