Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Relational Databases
Expertise: Beginner
May 13, 1997

Two-table Update

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

I assume you want to update rows in one table using matching rows (keys or matching fields) from another table. In Paradox, doing this is easy with QBE. For example:
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 the misleading error message "Capability not supported." This might lead you to believe that SQL doesn't support this. I had the same thought until I stumbled onto the solution when I started working with subqueries, which are discussed below.

What I'm going to present is not available in 16-bit Delphi; only 32-bit. This is because LocalSQL in the BDE included with Delphi 1.x is an extremely trimmed down subset of SQL that is marginally SQL89 compliant. In SQL89, subqueries are not available. In Delphi 2.0, the BDE was updated to support most of the features of SQL92, which made subqueries possible.

Clarification: Subqueries

A subquery is a query statement written within the WHERE or HAVING clause of another SQL statement. A SQL statement with a subquery returns a dataset that is based upon the results 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 set of data where you don't know what the set of data is beforehand. For example, let's say I have two tables. The first is a list of hospitals and contains general contact and address information, and most importantly, the estimated monthly budget assigned to each hospital. The second is a detail table containing the hospital's periodic financial data, to which each hospital adds info on a daily basis.

Now, let's say I want to make the following request:

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

If you didn't know about subqueries, you would most probably do this in a few of steps. First, you might join the two tables. Then you'd SUM on the current cost, then you'd do another SELECT to retrieve the rows from the second dataset whose COST column is greater than the BUDGETED column. Pretty inefficient.

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

SELECT HospitalID 
FROM HOSPITAL
WHERE 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 you must follow regarding subqueries:

  • Only a SELECT statement is allowed in a subquery. UNIONs are not 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 statement includes only one column, this isn't an issue.
  • A subquery can contain another, nested subquery. This is useful 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 you can and can't do with subqueries. I highly recommend LAN Times Guide to SQL by James Groff and Paul Weinberg. It's a great book that explains some really complicated stuff in a very simple manner.

Two-table Updates

It was necessary to establish a foundation above before going into the main topic of this discussion. So now that we have the mechanics down, we can get down to business performing two-table updates.

As you know, the SQL equivalent to the Paradox QBE ChangeTo is an UPDATE SQL statement. 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 SQL statement. Fortunately, they're not limited to residing in just a SELECT query; you can use them in UPDATE queries as well, and a subquery is how you perform a two-table update.

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

UPDATE Table1
UPDATE Table1
SET 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, the WHERE clause with the EXISTS statement isn't necessary. The existence of a match is checked to see if a match actually occurs between the two tables. This is actually a more SQL92 compliant statement. However, for BDE applications, you can forego the existence check. It seems the BDE performs this internally, so you might just be doubling your work.

What's happening behind the scenes is that the subquery produces an in-memory table that is scanned for each matching value. Then, the appropriate row is updated in Table1 based upon the scan.

A Final Note

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

DevX Pro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date