Hacker’s Guide to Visual FoxPro
An irreverent look at how Visual FoxPro really works. Tells you the inside scoop on every command, function, property, event and method of Visual FoxPro.
Update-SQL
This is one of several SQL commands added in Visual FoxPro. This one behaves pretty much like the Xbase REPLACE command—it updates one or more fields in one or more records of a single table.
Usage
UPDATE [ DatabaseName! ] TableName
SET FieldName1 = uExpr1
[, FieldName2 = uExpr2 [, ... ] ]
[ WHERE lCondition ]
|
Parameter |
Value |
Meaning |
|
DatabaseName |
Name |
Name of the database containing TableName. |
|
TableName |
Name |
The table containing the fields to be updated. |
|
FieldNamex |
Name |
The xth field to be updated. |
|
uExprx |
Same type as FieldNamex |
An expression to evaluate to assign a value to FieldNamex. |
|
lCondition |
Logical |
An expression determining which records get updated. |
Except in a few minor details, UPDATE in Visual FoxPro acts like the Xbase REPLACE command. The SET clause takes the place of the “field WITH value” piece, and the WHERE clause fills in for the FOR clause. In fact, REPLACE seems more powerful since it also supports scope and WHILE clauses (not to mention replacing in multiple tables at once, though that’s not a very good idea). On the other hand, by using a sub-query in the WHERE clause, you can do some pretty powerful things with UPDATE.
Beware of one big difference between them. If you REPLACE without indicating what records you’re interested in, only the current record is affected. UPDATE with no WHERE clause changes every record in the table.
The help hints—and our tests confirm—that REPLACE is generally faster than UPDATE. In exclusive mode, the differences are small, but with shared tables, we see tremendous differences. The reason for the difference is that UPDATE uses record locking while REPLACE locks the entire table. There may be cases where you can’t lock the table, so UPDATE’s behavior can save your skin in spite of its performance consequences.
UPDATE isn’t affected by the weird Xbase behavior that afflicts REPLACE when you’re changing a value in another work area and the current work area is at EOF(). UPDATE is unaffected by this nonsense. See REPLACE for a full explanation of the problem.
Example
* This is the same example given for REPLACE.
* Here's the equivalent using UPDATE.
* Suppose the area code for a bunch of phone numbers
* changes. You need to update those records.
* Assume you've stored the exchanges that are changing
* in a table, EXCHANGES, with one field, Exchange, and
* a tag on that field.
* The old area code is stored in cOldCode.
* The new area code is stored in cNewCode
USE PhoneList
UPDATE Exchanges SET AreaCode = cNewCode ;
WHERE AreaCode = cOldCode ;
AND LEFT(Phone,3) IN (SELECT Exchange FROM Exchanges)