UPDATE - SQL Command
Updates records in a table with new values.
Note
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 ...]
Parameters
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.
Remarks
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.
Legacy Code Examples
Example 1
The following example uses the OPEN DATABASE command to open the Visual FoxPro sample database and USE command to open the open the Customer table in TestData.dbc. The SQL UPDATE statement updates all the values in the MaxOrdAmt field to "25".
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'Data\TestData')
USE Customer
UPDATE Customer SET MaxOrdAmt = 25
BROWSE FIELDS Company, MaxOrdAmt
For more information, see USE Command and BROWSE Command.
Example 2
The following example uses OPEN DATABASE command to open the Visual FoxPro sample database, TestData.dbc. The SQL SELECT command retrieves data from the Products table in TestData.dbc, stores it in a table, MyProductsList, and displays the new table.
The CREATE CURSOR command creates the MyUpdateTable with a Prod_Unit field containing an integer value of 10 and displays the table.
The SQL UPDATE command updates the In_Stock field for MyProductsList with the value from MyUpdateTable for the product name, "Chai". The SQL SELECT command returns and displays a query for MyProductsList showing the updated In_Stock field for the product, "Chai".
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'Data\TestData')
SELECT in_stock, prod_name ;
FROM Products ;
INTO TABLE MyProductsList.dbf
BROWSE
CREATE CURSOR MyUpdateTable (prod_unit I(10))
INSERT INTO MyUpdateTable (prod_unit) VALUES (10)
BROWSE
UPDATE MyProductsList ;
SET MyProductsList.in_stock = MyUpdateTable.prod_unit ;
FROM MyUpdateTable ;
WHERE MyProductsList.prod_name = "Chai"
SELECT * FROM MyProductsList
For more information, see OPEN DATABASE Command, BROWSE Command, CREATE CURSOR - SQL Command, and INSERT - SQL Command.
Code Examples
Example 3
The following example shows a subquery used as an expression in the SET clause of the UPDATE statement. The query updates all records in the products table. If there is no matching productID in the mfg_msrp table, the field unitprice is set to NULL.
UPDATE products ;
SET unitprice = ;
(SELECT (msrp*.90) ;
FROM mfg_msrp ;
WHERE mfg_msrp.productID = products.productID ;
AND mfg_msrp.discontinued = .f.)
Example 4
The following example shows a correlated update which only touches records that match the filter conditions in the WHERE clause.
UPDATE products ;
SET unitprice = mfg_msrp.msrp*.90 ;
FROM mfg_msrp ;
WHERE mfg_msrp.productID = products.productID;
AND mfg_msrp.discontinued = .f.
Example 5
The following an example of a correlated update in which the update results include only the first match found for a record. Other matches for the record are ignored. The example code specifies two cursors. The first is the target for the updates. The second is the source of the updates and contains two changes for the first record in the target cursor and one update that does not match any records in the target cursor. In the results, the value of the first record shows the first match of .50 and not 10.00, the value of the second match.
CLOSE DATABASES ALL
CREATE CURSOR MyProducts (ProdID I , ProdCategory I NULL, MSRP Y NULL)
INSERT INTO MyProducts VALUES (1,9,1.00)
INSERT INTO MyProducts VALUES (2,8,2.00)
INSERT INTO MyProducts VALUES (3,7,3.00)
CREATE CURSOR MyUpdates (ProdID I , MSRP Y)
INSERT INTO MyUpdates VALUES (1,.50) && Matches and updates.
INSERT INTO MyUpdates VALUES (2,20.00) && Matches and updates.
INSERT INTO MyUpdates VALUES (4,40.00) && No match
INSERT INTO MyUpdates VALUES (1,10.00)&& 2nd match but no update.
UPDATE MyProducts SET MSRP=MyUpdates.MSRP FROM MyUpdates WHERE MyProducts.ProdID=MyUpdates.ProdID
SELECT MyProducts
BROWSE