Question:
I’m a Paradox for Windows convert. In Paradox it’s a relatively simple thing to create a query that changes data in a field of one table with the values in a similar column from another table using matching values in specific columns as reference keys. In Delphi, however, I’ve discovered that I am pretty much relegated to using SQL and it isn’t at all obvious how to go about doing a two-table update. Is there a way to do it?

Answer:

Getting at the Problem

Table1.DB | KeyField  | UpdateField        |          | _example1 | ChangeTo _example2 |Table2.DB | KeyField  | SourceField |          |_example1  | _example2   |

If you set up this query in the Database Desktop that comes with Delphi, you’re most likely to get themisleading error message “Capability not supported.” This mightlead you to believe that SQL doesn’t support this. I had the same thought until I stumbled onto the solution when I started working withsubqueries, which are discussed below.

What I’m going to present is not available in 16-bit Delphi; only32-bit. This is because LocalSQL in the BDE included with Delphi 1.xis an extremely trimmed down subset of SQL that is marginally SQL89compliant. In SQL89, subqueries are not available. In Delphi 2.0, theBDE was updated to support most of the features of SQL92, which madesubqueries possible.

Clarification: Subqueries

A subquery is a query statement writtenwithin the WHERE or HAVING clause of another SQL statement. ASQL statement with a subquery returns a dataset that is based upon theresults of another query. It’s really running a query within a query; hence,the name subquery is entirely appropriate.

Subqueries are useful for retrieving information from a table based on a setof data where you don’t know what the set of data is beforehand. Forexample, let’s say I have two tables. The first is a list of hospitalsand contains general contact and address information, and most importantly, theestimated monthly budget assigned to each hospital. The second is adetail table containing the hospital’s periodic financial data, to which eachhospital adds info on a daily basis.

Now, let’s say I want to make thefollowing request:

Give me a list of the hospitals for which total costs exceed the estimatedbudget.

If you didn’t know about subqueries, you would most probably do this in afew of steps. First, you might join the two tables. Then you’d SUM on thecurrent cost, then you’d do another SELECT to retrieve the rows from thesecond dataset whose COST column is greater than the BUDGETED column. Prettyinefficient.

With a subquery, you’d answer the question like this:

SELECT HospitalID FROM HOSPITALWHERE BUDGETED < (SELECT SUM(INCURRED_COST) FROM HOSP_COST                  WHERE HOSP_COST.HospitalID = HOSPITAL.HospitalID)

With this query, you’ve answered the question in one step instead of three. Much more efficient!

Before we go on about two-table updates, here are some rules youmust follow regarding subqueries:

• Only a SELECT statement is allowed in a subquery. UNIONs arenot allowed (besides, the BDE doesn’t support UNIONS).
• A subquery can return only one column of data as its result.
• ORDER BY cannot be used with a subquery. However, since the SELECT statementincludes only one column, this isn’t an issue.
• A subquery can contain another, nested subquery. This isuseful for further limiting the rows returned by the subquery,thus limiting the final result set.

I suggest you get a book on SQL for more detailed information on what youcan and can’t do with subqueries. I highly recommend LAN Times Guide toSQL by James Groff and Paul Weinberg. It’s a great book that explains somereally complicated stuff in a very simple manner.

Two-table Updates

It was necessary to establish afoundation above before going into the main topic of this discussion. So now thatwe have the mechanics down, we can get down to business performingtwo-table updates.

As you know, the SQL equivalent to the Paradox QBE ChangeTo is an UPDATE SQLstatement. An UPDATE has the following structure:

UPDATE [Table]SET [Field] = [Some Value]WHERE [Some Condition Exists]

As I mentioned above, subqueries reside in the WHERE portion of a SQLstatement. Fortunately, they’re not limited to residing in just a SELECTquery; you can use them in UPDATE queries as well, and a subquery is how youperform a two-table update.

For our example, let’s do a simple update that updates the values of onecolumn in a table from another where the key fields of both match. This isthe typical two-table update. Here’s the SQL:

UPDATE Table1UPDATE Table1SET UpdateField = (SELECT SourceField FROM Table2                   WHERE Table2.KeyField = Table1.KeyField)WHERE EXISTS (SELECT SourceField FROM Table2                   WHERE Table2.KeyField = Table1.KeyField)

As you can see, this is not that complex. In fact, with the BDE, theWHERE clause with the EXISTS statement isn’t necessary. The existence of amatch is checked to see if a match actually occurs between the two tables.This is actually a more SQL92 compliant statement. However, for BDEapplications, you can forego the existence check. It seems the BDE performsthis internally, so you might just be doubling your work.

What’s happening behindthe scenes is that the subquery produces an in-memory table that is scannedfor each matching value. Then, the appropriate row is updated in Table1 basedupon the scan.

A Final Note

Subqueries can be slow, especially those on a single table. If youhave extremely large datasets to work with, especially with local databaseslike Paradox or dBase, I advise you to evaluate the performance of asubquery versus a few smaller queries. The reason for this is that local PCdatabases are notoriously slow in the first place, and not optimized forthis type of operation. However, I’ve done this stuff on smaller tables thathave only 100K records, and they work just fine.