UPDATE Target

Specifies a target table, cursor, table or cursor alias, or file to update. You can include multiple sources of data for the update operation in the FROM clause. Target can have the following syntaxes:

• [DatabaseName!]TableName
  
  DatabaseName! specifies the name of a database containing the table if the table is in a noncurrent database. If the table is in a noncurrent database, you must include the name of the database. Use an exclamation point (!) as a delimiter immediately following the database name and preceding the table name.
  
  TableName specifies the name of a table for the update operation.
  
  
• Alias
  
  Alias specifies an alias that matches a table in the FROM clause or a cursor in the current data session for the update operation.
  
  
• FileName
  
  FileName specifies the name of a file for the update operation.
  
  

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. If you want to use an object property in an expression, you need to specify a memory variable and then use that variable in the expression. For example, you can specify x = oColField("iid").Value and then use the SET clause set iid = x in your UPDATE - SQL command. If you use the object property, that is objectname.property, directly in the expression, the command uses it as an alias and fails. 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 - FROM Clause.

Note:
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 [FORCE] Table_List_Item[[, ...] | [JOIN [ Table_List_Item]]]

Specifies one or more tables containing the data for the update operation. The FROM clause has the same syntax as in the SQL SELECT command except for the following restrictions:

• The target table or cursor cannot be included in an OUTER join as a secondary table or cursor.
  
  
• It should be possible to evaluate all other JOIN operations before performing a JOIN operation on the target table.
  
  
• The target cursor cannot be the result from a subquery.
  
  

For more information, see SELECT - SQL Command. FORCE specifies that the tables in the table list are joined in the order they appear in the FROM clause.

Note:
If FORCE is omitted, Visual FoxPro attempts to optimize the update operation. However, the update operation might be executed faster by including the FORCE keyword to disable Visual FoxPro update optimization.

Table_List_Item can have the following syntaxes:

• [DatabaseName!]Table [[AS] Local_Alias]
  
  DatabaseName! specifies the name of a database containing the table if the table is in a noncurrent database. If the table is in a noncurrent database, you must include the name of database. Use an exclamation point (!) as a delimiter immediately following the database name and preceding the table name.
  
  Table specifies the name of the table or cursor you want to update data from. If no table is open, Visual FoxPro displays the Open dialog box so you can specify the file location. After the table opens, it remains open when the query is complete.
  
  Local_Alias specifies a temporary name for the table specified in Table. If you specify a local alias, you must use the local alias instead of the table name in the UPDATE statement. The alias can represent a table or a cursor.
  
  JOIN provides the capability for specifying one or more secondary tables. There is no hard coded limit on the number of tables, aliases, 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 in SELECT statements, see the FROM clause in SELECT - SQL Command.
  
  

WHERE FilterCondition1 [AND | OR FilterCondition2 ...]]

Specifies one or more filter conditions that records must meet to be updated with new values. There is no limit to the number of filter conditions in the WHERE clause. To reverse the value of a logical expression, use the NOT operator. To check for an empty field, use the EMPTY(В ) function. For more information, see EMPTY( ) Function.

To determine the number of records updated, check the value of the _TALLY system variable immediately after executing the SQL UPDATE command. For more information, see _TALLY System Variable.

Tip:
When updating multiple records in a table opened for shared access, SQL UPDATE uses record locking, unlike the REPLACE command. This reduces record contention in multiuser situations but might reduce performance. For maximum performance, open the table for exclusive use or use the FLOCK(В ) function to lock the table. For more information, see FLOCK( ) Function.

In the SET clause, Visual FoxPro processes assignment sequentially. If you issue the command, UPDATE table1 SET field1 = field2, field2=field1, the values of the two fields are not swapped, rather the values of F1 and F2 are the same.