Docked

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)

See Also

Delete-SQL, Replace, Select-SQL

View Updates

Copyright © 2002 by Tamar E. Granor, Ted Roche, Doug Hennig, and Della Martin. All Rights Reserved.