Old-fashioned JOIN and UPDATE commands 4

[Mike Lewis]

In thread184-1805753, we have been trying to solve a problem in Foxpro 2.6, and we discussed the possibility of using certain commands which are now flagged as compatibility only. That led to the question of whether such commands still function correctly in VFP 9.

The commands in question were JOIN and UPDATE. Don't confuse these with the SQL equivalents. They are quite different.

I have now tested both commands in VFP 9. First JOIN.

CREATE CURSOR Employees (ID I, Emp_Name C(10))
INSERT INTO Employees (ID, Emp_Name) VALUES (1, "Lucy")
INSERT INTO Employees (ID, Emp_Name) VALUES (2, "Ricky")
INSERT INTO Employees (ID, Emp_Name) VALUES (3, "Fred")
INSERT INTO Employees (ID, Emp_Name) VALUES (4, "Ethel")

CREATE CURSOR Salaries (ID I, Salary Y)
INSERT INTO Salaries (ID, Salary) VALUES (1, 1500)
INSERT INTO Salaries (ID, Salary) VALUES (2, 1600)
INSERT INTO Salaries (ID, Salary) VALUES (3, 1750)
INSERT INTO Salaries (ID, Salary) VALUES (4, 1800)

SELECT Employees
JOIN WITH Salaries TO Personnel FOR Employees.ID = Salaries.ID

In Short, JOIN looks at each record in the table in the current work area (Employees); for each one, it looks for a matching record in a second table (Salaries); it then writes a record to a new table (Personnel) which contains all the field from both tables. The FOR clause defines the matching field.

So, in this example, Personnel will look like this:

ID      Emp_Name     Salary
==      =========    ======
1      Lucy          1500
2      Ricky         1600
3      Fred          1750
4      Ethel         1800

This seems to work as expected.

There are a couple of optional extras:

You can add a FIELDS clause to define a list of the fields that are to be included in the new table. These can be fields from either existing table, but if they are from the WITH table (Salaries), they must be qualified with the alias, even if the name is unique between the tables.

You can extend the FOR clause to include other conditions, which can be used to filter the result. For example, the following version will result in a table that only contains records for Ricky, Fred and Ethel:

JOIN WITH Salaries TO Personnel FOR Employees.ID = Salaries.ID AND Salaries.Salary > 1500

OK, now the UPDATE command.

CREATE CURSOR Employees (ID C(1), Emp_Name C(10), Salary Y)
INDEX ON ID TAG ID
INSERT INTO Employees (ID, Emp_Name) VALUES ("1", "Lucy")
INSERT INTO Employees (ID, Emp_Name) VALUES ("2", "Ricky")
INSERT INTO Employees (ID, Emp_Name) VALUES ("3", "Fred")
INSERT INTO Employees (ID, Emp_Name) VALUES ("4", "Ethel")

CREATE CURSOR Salaries (ID C(1), Salary Y)
INDEX ON ID TAG ID
INSERT INTO Salaries (ID, Salary) VALUES ("1", 1500)
INSERT INTO Salaries (ID, Salary) VALUES ("2", 1600)
INSERT INTO Salaries (ID, Salary) VALUES ("3", 1750)
INSERT INTO Salaries (ID, Salary) VALUES ("4", 1800)

SELECT Employees
UPDATE ON ID FROM Salaries REPLACE Employees.Salary with Salaries.Salary

In this case, we are updating the table in the current work area (Employees) rather than creating a new table. The FROM clause specifies a table (Salaries) from which we obtain the new values. The ON clause specifies a linking field (ID). This field must be present in both tables, and must have the same name in both. You cannot use an expression here; only a single field.

Here is the result:

ID      Emp_Name     Salary
==      =========    ======
1      Lucy          1500
2      Ricky         1600
3      Fred          1750
4      Ethel         1800

So, the same end-product as with JOIN, but in this case all the data is in the original table.

By default, UPDATE expects there to be an index on the linking field in both tables. If you add the keyword RANDOM, then you don't need an index on the FROM table. Presumably, this is only for purposes of efficiency; it does not affect the result. Whether it actually makes a difference to performance in VFP is something that I haven't tested.

You will notice that, in my UPDATE example, the ID fields are character fields. I originally used integers for the ID, but this produced an error message: "The key field used to UPDATE ON must be of type Character, Date or Numeric". In other words, the command doesn't recognise an integer data type as numeric, which is interesting.

What's also interesting is that that error message, which relates specifically this version of UPDATE, is listed in the Help for VFP 9 (error 1145),although the Help for the actual command simply says it is for backward compatibility.

One final point. Intellisense doesn't seem to recognise these two commands.

Of course, all of the above is completely academic for anyone using Visual Foxpro, as we now have better ways of doing things. But the information might be useful for users of pre-Visual versions - not to mention a bit of nostalgia for the rest of us.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Yes, this works. AS you said, I might not even try to see whether this is faster than an SQL join only needing such a match, too. I usually prefer INTO CURSOR as query result target and sometimes even arrays, but rarely tables. And it's really just a special case. UPDATE could be handier, but then I would perhaps use a relation or a few relations and the REPLACE command instead if doing it the xBase way. With the possibility to replace in multiple tables/workareas, too.

Bye, Olaf.



Olaf Doschke Software Engineering

https://www.doschke.name

 

[TamarGranor]

This is (part of) what we had to say about these in HackFox:

"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."

The third command is TOTAL.

Tamar

 

[Olaf Doschke]

I can understand why JOIN is slow, creating a new DBF always had an overhead, though any query INTO cursor does so, it does so in memory as long as cursors can be held in memory, and since today you could resver 2GB process memory it's most often.

But update on a single matching field, in an existing table? I mean, it's mainly just a short form of the SQL update where you need to provide/specify the index tag, like you need to do with SET RELATION And like you usually will know anyway, as its the primary key of one and the foreign key of another table, just the usual relationship of these keys.

Wouldn't this run at least similarly fast as an SQL Update of that specific simple, but also very common join?

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Mike Lewis]

Tamar, just to be clear .... I am not advocating the use of these commands. I posted the details partly to satisfy my own curiosity and partly because, in another thread, we were speculating on how well such commands work in modern versions of Foxpro.

That said, I can understand that they were considered slow in Foxbase days. In particular, UPDATE ... RANDOM would need to look at every record in the first table, and then look through all the records in the second table until a match was found. In the worst case, that's like a Cartesian join. The JOIN command would do something similar (there is no mention of JOIN requiring indexes).

But I wonder how significant that would be with small to medium tables on a modern computer. Foxpro 2.x users might at least consider these commands, if only as an alternative to other ways of doing things.

Mike




__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[TamarGranor]

I tested those back in VFP 3 days, but I haven't looked at them since. So yeah, on modern machines, for small data sets, they might not be awful.

Tamar