Updating Two or More Fields Through a Join

Hey, Ted:

I appreciated your suggestion to use COALESCE when updating a file through a join. But I was curious about which one is more efficient? I have to guess that it's COALESCE, because it doesn't appear to be executing another select with the EXISTS clause.

But my real problem is updating more than one field based on another file. I have had occasion to update two or more fields in one file, based on another, and have used the EXISTS solution, something like this:

UPDATE MLIB/PRODFILE A 
   SET FIELD1 = 
      (SELECT NEWFIELD1 
         FROM FIXES B 
        WHERE A.CO# = 1 AND A.CUST# = B.CUST#), 
   SET FIELD2 = 
      (SELECT NEWFIELD2 
         FROM FIXES C 
        WHERE A.CO# = 1 AND A.CUST# = C.CUST#) 
 WHERE EXISTS 
      (SELECT * 
         FROM FIXES D 
        WHERE A.CO# = 1 AND A.CUST# = D.CUST#) 

This one always merits a "what are you doing?" call from the administrator. What can I do to make this less processor-intensive?

--Zeff

My guess is that performance suffers because you access the FIXES file three times. You can eliminate some of that overhead by updating both fields with one SET clause. Here's an example that uses fields that would appear more familiar to most readers.

Say a customer master file that contains, among other things, address fields--two street address fields, city, state, and postal (ZIP) code.

create table custmaster 
  (custnbr dec(5),             
   custname char(20),          
   custaddr1 char(20),         
   custaddr2 char(20),         
   custcity  char(14),         
   custstate char(2),          
   custzip   char(10),         
. . .  more fields omitted . . .
   primary key(custnbr))       

Let's say further that we have a file with new addresses that need to be applied to the customer master.

create table newaddr 
  (custnbr dec(5),          
   addr1 char(20),          
   addr2 char(20),          
   city  char(14),          
   state char(2),           
   zip   char(10),          
   primary key(custnbr))    

In this example, there are five fields that need to be updated based on customer number. To carry out the update, put the names of the five fields in the SET clause and surround them with parentheses. Assign five values to the field list enclosed in parentheses.

update custmaster as c                                  
   set (custaddr1, custaddr2, custcity, custstate, custzip) =  
   (select addr1, addr2, city, state, zip                      
      from newaddr as na                                
    where c.custnbr = na.custnbr)                              
where custnbr in 
      (select custnbr from newaddr) 

There is another shortcut you may be interested to know about. If you want to replace all the fields in a record, you can follow SET with the word ROW.

update custmaster
set row = (1, "Jax Hamburgers", . . . other values omitted . . .)

To address your question about efficiency, it's always hard to say what's most efficient. One of the great things about SQL is that it relieves human beings of the chore of figuring out the mechanics of data manipulation and retrieval. That is, we tell the computer what we want and it figures out how to do it.

When I'm in doubt about which of two methods is better, I run them both and see which runs better. In many cases, it won't matter. The database engine often converts queries from one type to another, anyway. For instance, you may code a SELECT clause after an IN predicate, but the database manager may change it to an inner join.

Another thing to keep in mind is that efficiency changes from release to release as the database wizards in Rochester monkey with the SQL engine.

When a query runs fine, leave it alone. When it performs poorly, use debug or Visual Explain to figure out how to make it work better.

--Ted

Sponsored By SUSE LINUX

Mainframe Linux for IBM Midrange Systems · Available for i/p/z/x Series · Mainframe stability · Access to legacy data SuSE Linux Enterprise Server lets you keep your current environment and add the power of Linux. Utilize the best of both worlds, exchange data between each OS, all on one system. Get and evaluation copy today www.suse.com

---

Editors: Howard Arner, Joe Hertvik, Ted Holt, David Morris,

Managing Editor: Shannon Pastore

Publisher and Advertising Director: Jenny Thomas

Advertising Sales Representative: Kim Reed

Contact the Editors: To contact anyone on the IT Jungle Team
Go to our contacts page and send us a message.

---