UPDATE SQL Command

Updates records in a single DBF file with new values.

UPDATE Table_Name
SET Column_Name1 = eExpression1 [, Column_Name2 = eExpression2 ...]
[FROM Table_List_Item [[, ...] | [JOIN [ Table_List_Item]]]
WHERE FilterCondition1 [AND | OR FilterCondition2 ...]

Parameters

UPDATE Table_Name

Specifies a target DBF file to update. You can include multiple sources of data for the update operation in the FROM clause.

SET Column_Name1= eExpression1 [, Column_Name2 = eExpression2 ...]

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.

If you use a subquery in the SET clause, you cannot use subqueries in the WHERE clause. A subquery in the SET clause must meet exactly the same requirements as subqueries used in comparison operations.
[FROM Table_List_Item[[, ...] | [JOIN [ Table_List_Item]]]

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.
WHERE FilterCondition1 [AND | OR FilterCondition2 ...]]

Specifies one or more filter conditions that records must meet to be updated with new values. Filter conditions number is not limited.

 

UPDATE Example

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 "D:\Data\orders.dbf" SET order_amt=0 WHERE NOT EMPTY(to_city) AND cust_id IS NULL

Update the field with the results of another query:

UPDATE "D:\Data\orders.dbf" SET order_amt= (SELECT SUM(unit_price) FROM "D:\Data\orditems.dbf" WHERE orditems.order_id IS NOT NULL)

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):

UPDATE "D:\Data\orders.dbf" SET orders.emp_id=orditems.quantity FROM orditems WHERE VAL(orders.ship_via)=orditems.line_no