Updates records in a table with new values.
The SQL UPDATE command can update records for a single table only.
UPDATE Target SET Column_Name1 = eExpression1 [, Column_Name2 = eExpression2 ...] [FROM [FORCE] Table_List_Item [[, ...] | [JOIN [ Table_List_Item]]] WHERE FilterCondition1 [AND | OR FilterCondition2 ...]
- 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! 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 specifies an alias that matches a table in the FROM clause or a cursor in the current data session for the update operation.
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").Valueand then use the SET clause
set iid = xin 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.
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.
FORCE specifies that the tables in the table list are joined in the order they appear in the FROM clause.
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.
- 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.