Use SQL to Easily Update Multi-Key Files

July 27, 2005 Hey, Ted

Updating a single record in a file that has a lot of key fields often requires SQL commands with complicated WHERE clauses. I use a technique with web applications that greatly simplifies the WHERE clause. I have never seen this technique published before, so I hope you can use it.

When I retrieve a record that will be updated through a Web browser, I select the relative record number along with the data.

SELECT RRN(MyFile) as storedRRN, MyFile.* 
  FROM MyFile 
 WHERE ENBBTYPE = 'COPY' and ENBBTCH# = 4062

When I update, I need only refer to the relative record number.

UPDATE MyFile 
   SET SomeCode = 'FRO', Description = 'Front' 
 WHERE RRN(MyFile) = storedRRN

–Chuck Wakelee, Cardone Industries

Since I favor making things as simple as possible, I am glad to publish Chuck’s tip. It may not be worth my mentioning it, but Chuck’s SQL statements are bare-bones illustrations. To use this technique in a program will require the use of host variables.

–Ted