Updating through a Join with SQL

Hey, Ted:

Is it possible to use SQL to update a database field in one physical file with a value from another file based on common join field values?

--Mark

Yes. This is a common question and its answer is a good technique to know. Here's an illustration.

Suppose the XYZ Corporation is reducing the number of sales regions. They need to update the customer master file by replacing an existing region number with another region number. For example, all customers in region 4 are to be reassigned to region 1, and all customers in region 5 are to be reassigned to region 3. Let's further assume that there are hundreds of such reassignments, lest I get email pointing out that it would be quick to execute a couple of update statements and be done with it. The reassignments are stored in a physical file called FIXES, which has two fields--old region number and new region number.

Here's one SQL command that will accomplish the task:

update customer as c         
   set region =                    
      (select newregion            
         from fixes          
        where oldregion = c.region)
 where exists                      
      (select *                    
         from fixes          
        where oldregion = c.region)

There is some redundancy in the query, but without EXISTS, the query will set the region number to null in the records of customers whose regions are not being reassigned.

Here is an alternate syntax that doesn't require the EXISTS predicate.

update customer as c                          
   set region =                                     
      coalesce((select newregion                    
                 from fixes                   
                where oldregion = c.region), region)

In this case, if the region is being reassigned, the region is set to the new region in the FIXES file. But if the region is not being reassigned (i.e., there is no matching record in FIXES), the region is set to its current value. The COALESCE function, which chooses the first non-null value from a list, makes it all possible. One thing that I like about this technique is that the first argument of COALESCE is an SQL SELECT command. IBM has enhanced SQL/400 to permit more and more embedded SELECT statements, which makes for more powerful SQL.

One big difference between these two queries is that the first one only updates the changed records, while the second query updates all the records in the physical file.

--Ted

Sponsored By WORKSRIGHT SOFTWARE

Do you need area code information? Do you need ZIP Code information? Do you need ZIP+4 information? Do you need city name information? Do you need county information? Do you need a nearest dealer locator system? We can HELP! We have affordable AS/400 software and data to do all of the above. Whether you need a simple city name retrieval system or a sophisticated CASS postal coding system, we have it for you! The ZIP/CITY system is based on 5-digit ZIP Codes. You can retrieve city names, state names, county names, area codes, time zones, latitude, longitude, and more just by knowing the ZIP Code. We supply information on all the latest area code changes. A nearest dealer locator function is also included. ZIP/CITY includes software, data, monthly updates, and unlimited support. The cost is $495 per year. PER/ZIP4 is a sophisticated CASS certified postal coding system for assigning ZIP Codes, ZIP+4, carrier route, and delivery point codes. PER/ZIP4 also provides county names and FIPS codes. PER/ZIP4 can be used interactively, in batch, and with callable programs. PER/ZIP4 includes software, data, monthly updates, and unlimited support. The cost is $3,900 for the first year, and $1,950 for renewal. Just call us and we'll arrange for 30 days FREE use of either ZIP/CITY or PER/ZIP4. WorksRight Software, Inc. Phone: 601-856-8337 Fax: 601-856-9432 E-mail: software@worksright.com Web site: www.worksright.com