Join, Total, Update

As long as we’ve been using Xbase, we’ve heard that these three commands should be avoided. At least as far back as FoxBase+, the manuals warn that JOIN may be very slow and can overrun available disk space.

As a result of these dire warnings, we never looked very hard at these commands (though we’ve occasionally been called on to maintain code that uses them). Now that we’ve done so, we’ve decided it’s just as well. They’re hard to use, limited in capability, and can be extremely slow.

Usage

JOIN WITH cAlias | nWorkArea TO cTable
        FOR lForExpression
        [ FIELDS cFieldList ]
        NOOPTIMIZE

JOIN is sort of a limited version of SELECT-SQL. It matches up records in a pair of tables based on some condition and creates a new table (cTable) containing fields from both the original tables. One table must be open in the current work area; the other must be open in another work area. The FOR clause determines how the records are joined. You can limit the fields in the result using the FIELDS clause, but JOIN can’t handle memo or general fields, and you can’t use expressions in the field list.

For very small data sets, JOIN can be competitive in speed with SELECT, but as soon as you get beyond a handful of records, it bogs down. The thought of throwing NOOPTIMIZE into the mix is really frightening.

JOIN has a bug when dealing with long field names. In the newly created table, all field names are truncated to 10 characters or less. This makes some sense since the table is created as a free table.

Frankly, we can’t see any reason to use JOIN ever. Anything you can do with it, you can do more easily with SELECT-SQL. Even Microsoft thinks so, as they’ve labeled this command “for backward compatibility,” and suggest you use SELECT-SQL.

Example

* Here's an example involving very small tables so you
* can see the result in this lifetime. It fails in
* versions VFP 5 and later, but does work in VFP 3.
USE category
SELECT 0
USE products
JOIN WITH category TO temp FOR category_id=category.category_id

* Here's the SELECT-SQL equivalent:
SELECT * FROM category, products ;
   WHERE category.category_id=products.category_id ;
   INTO TABLE temp

Usage

TOTAL TO cTable ON uField
        [ FIELDS nNumericFieldList ]
        [ Scope ]
        [ FOR lForExpression ]
        [ WHILE lWhileExpression ]
        [ NOOPTIMIZE ]

TOTAL is similar to working with GROUP BY in SELECT-SQL, but far more limited. It groups records from the table open in the current work area based on the listed field (uField) and totals the numeric fields listed in nNumericFieldList. If FIELDS is omitted, all numeric fields are totaled. A new table is created to hold the result. All fields from the original table are included in the result. You can limit the records included in the computation using the Scope, FOR and WHILE clauses.

So what else is wrong with TOTAL? The new table created has the same structure as the original. If the total for a field is too big to fit, you lose precision. Also, since all fields are carried along, you get random results for fields that aren’t either totaled or listed in the ON clause. (Actually, this is the same thing that happens when you include extraneous fields in a GROUPed query, but at least there you have a chance to omit them.) TOTAL is limited to working with a single table—you can’t match up records from multiple tables. Finally, you can’t compute expressions, only sum existing fields, so you can’t do things like total (quantity*unit price) to get an invoice total.

Bottom line: Like JOIN, we can’t see a lot of reasons to use TOTAL.

Example

* We couldn't find a way to use TOTAL with the sample
* TasTrade database that comes with Visual FoxPro.
* The table in this example is from the Tutorial
* data that shipped with earlier versions of FoxPro.
USE Detail       && Invoice detail lines
* Compute invoice totals
TOTAL ON ino TO InvTotal FIELDS Ltotal

Usage

UPDATE ON uJoinFieldName FROM cTable
        REPLACE uFieldName1 WITH eExpression1
        [ , uFieldName2 WITH eExpression2 [ , ... ] ]
        [ RANDOM ]

UPDATE is a sort of early way of creating a relation between two tables and using it to modify data. The table in the current work area gets updated based on establishing a relation between it and another open table. The relation is based only on a single identically named field (uJoinFieldName) in the two tables. The current table must be ordered on the common field, either with an index or the natural order of the records. The other table (cTable) should also be ordered on the common field. If not, you must include the RANDOM keyword.

For each matching record found in cTable, each listed field is updated as specified in the REPLACE clause. The expression eExpressionn can reference fields from both tables (actually, we suppose, from any open tables).

Got all that? It took us a while. This is an incredibly complicated command to do something that there are much easier ways to do. UPDATE-SQL lets you do similar things a lot more simply since you can specify the relationship explicitly in its WHERE clause. But, even in FoxPro 2.x, you could do this sort of thing by setting a relation and then using REPLACE.

In addition to the difficulty of using it, in a properly normalized database, there aren’t likely to be a lot of places where you’d want to do this sort of computation. The only thing we can think of is updating a table from a copy, and that’s often easier in Visual FoxPro just by using the built-in buffering techniques.

We just can’t see any reason to use UPDATE.

Example

* Again, we can't see a way to use UPDATE with
* the nicely normalized TasTrade database.
* The example uses the old, less normalized,
* Tutorial data.
SELECT 0
USE Detail
SELECT 0
USE Invoices

* Clear out existing invoice totals.
REPLACE ALL iTotal WITH 0

* Now recompute.
UPDATE ON ino FROM Detail ;
   REPLACE iTotal WITH iTotal+Detail.Qty*Detail.Price

See Also

Replace, Select-SQL, Update-SQL