Updating Through A Join With SQL, Take Three

April 3, 2013 Ted Holt

As I was looking for something in the DB2 for i SQL reference, my eyes happened to see MERGE in the list of statements, and I realized that I had been doing something the hard way. I realized that I was about to abandon the method I had been using to update values in one database table (physical file) from data in another one.

Once in a while, someone requests that I use a list of values, which are usually stored in a spreadsheet, to mass update the database on the big computer. Fortunately, several years ago I learned how to use SQL to carry out such an update so that I don’t have to write a new RPG program every time. I have written about such updates before. See the Related Stories links at the bottom of this article.

The methods I published do what I need them to do, but they are not intuitive and require a lot of head- and belly-scratching, especially when they include the EXISTS predicate. Using MERGE is better because MERGE was designed for cross-table updates.

Let me illustrate by revisiting two examples that I used in my previous articles.

Suppose I have a table of customers that includes, among other things, a sales region column.

CUST ID   REGION
=======  ======
 1         100
 2         100
 3         200
 4         100
 5         200

The powers that be have taken the bold decision to renumber the sales regions. You have been asked to change the database in one broad stroke so that your employer won’t have to hire an army of temps to do the work manually.

Here, in a table named FIXES, is the new region numbering scheme.

OLDREGION  NEWREGION
=========  =========
   100         20
   200         21

And here is how you renumber the regions.

merge into customer as c
  using (select oldregion, newregion from fixes) as f
     on c.region = f.oldregion
  when matched then
     update set c.region = f.newregion

In the first line, you tell the computer that you are updating a table named CUSTOMER, which you call “c” for short.

In the second and third line, you identify that old (existing) and new (replacement) values and tell how they join to the CUSTOMER table. The old region column of the FIXES table must match the region column in the CUSTOMER table. Using correlation name “f” to identify the data from the FIXES table simplifies the SQL command and clarifies your intention.

In the fourth and fifth lines, you tell the computer how to update the database. When the computer finds a match, it is to update the REGION column in CUSTOMER with the value in the NEWREGION column of FIXES.

After the merge, the customer data looks like this:

CUST ID   REGION
=======   ======
 1          20
 2          20
 3          21
 4          20
 5          21

If there were customers with no match in FIXES, this MERGE statement would only update the customers that were in both tables. If you want to update customers that are not in the FIXES table, you will have to run a separate UPDATE command. Here are a couple of possibilities.

1. Set the sales region to null for those customers who are not in the FIXES table.

update customer as c
   set c.region = null
 where c.region not in
          (select oldregion from fixes)

2. Set the sales region to 99 for the customers who are not in the FIXES table.

update customer as c
   set c.region = 99
 where c.region not in
          (select oldregion from fixes)

The second example is more like the requests I usually get.

Assume that each vendor is assigned a type code of A, B, or C. Last week an accountant downloaded all the vendor data to a spreadsheet. He has modified the type code for some vendors and wants you to update the type code in the database.

Here’s the vendor data before the update.

ID   NAME                     TYPE
===  =======================  ====
101  TOLSTOY IMPORTS          A
102  DOSTOYEVSKY DONUTS       B
103  CHECKOV TIRE CENTER      A
104  GOGOL FOODS              B
105  TURGENEV SPORTING GOODS  C

Here are the new values.

ID     TYPE
===    ====
101    B
102    B
103    A
104    A
105    B

As you can see, Tolstoy lost ground, but Gogol and Turgenev have moved up in the world.

Here’s the command to update the database.

merge into vendors as v
  using (select id, type from vendorupdt) as up
     on v.id = up.id
  when matched then
     update set v.type = up.type
  else ignore

And here’s the updated database.

ID   NAME                     TYPE
===  =======================  ====
101  TOLSTOY IMPORTS          B
102  DOSTOYEVSKY DONUTS       B
103  CHECKOV TIRE CENTER      A
104  GOGOL FOODS              A
105  TURGENEV SPORTING GOODS  B

It sure beats the EXISTS predicate, doesn’t it?

For more information about MERGE, see Michael Sansoterra’s excellent article, Merge Into the Synchronization Fast Lane with DB2 for i 7.1.