• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • 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.

    RELATED STORIES

    Merge Into the Synchronization Fast Lane with DB2 for i 7.1

    Updating Through a Join with SQL, Take Two

    Updating through a Join with SQL

    Update One File Based on Another File



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    DRV Tech

    Get More Out of Your IBM i

    With soaring costs, operational data is more critical than ever. IBM shops need faster, easier ways to distribute IBM applications-based data to users more efficiently, no matter where they are.

    The Problem:

    For Users, IBM Data Can Be Difficult to Get To

    IBM Applications generate reports as spooled files, originally designed to be printed. Often those reports are packed together with so much data it makes them difficult to read. Add to that hardcopy is a pain to distribute. User-friendly formats like Excel and PDF are better, offering sorting, searching, and easy portability but getting IBM reports into these formats can be tricky without the right tools.

    The Solution:

    IBM i Reports can easily be converted to easy to read and share formats like Excel and PDF and Delivered by Email

    Converting IBM i, iSeries, and AS400 reports into Excel and PDF is now a lot easier with SpoolFlex software by DRV Tech.  If you or your users are still doing this manually, think how much time is wasted dragging and reformatting to make a report readable. How much time would be saved if they were automatically formatted correctly and delivered to one or multiple recipients.

    SpoolFlex converts spooled files to Excel and PDF, automatically emailing them, and saving copies to network shared folders. SpoolFlex converts complex reports to Excel, removing unwanted headers, splitting large reports out for individual recipients, and delivering to users whether they are at the office or working from home.

    Watch our 2-minute video and see DRV’s powerful SpoolFlex software can solve your file conversion challenges.

    Watch Video

    DRV Tech

    www.drvtech.com

    866.378.3366

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    BCD:  Recorded Webinar: How to mine IBM i report data & extract it to Excel
    Northeast User Groups Conference:  23nd Annual Conference, April 22 - 24, Framingham, MA
    COMMON:  Join us at the 2013 Conference & Expo, April 7 -10 in Austin, TX

    More IT Jungle Resources:

    System i PTF Guide: Weekly PTF Updates
    IBM i Events Calendar: National Conferences, Local Events, and Webinars
    Breaking News: News Hot Off The Press
    TPM @ The Reg: More News From ITJ EIC Timothy Prickett Morgan

    Attachmate Delivers Web Client for SFTP Tool Midrange Power7+ Servers: The IBM Sales Pitch

    Leave a Reply Cancel reply

Volume 13, Number 7 -- April 3, 2013
THIS ISSUE SPONSORED BY:

WorksRight Software
SEQUEL Software
Northeast User Groups Conference

Table of Contents

  • Bringing Home The Data
  • Updating Through A Join With SQL, Take Three
  • Admin Alert: A Checklist For Monitoring Your IBM i Environment

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Meet The Next Gen Of IBMers Helping To Build IBM i
  • Looks Like IBM Is Building A Linux-Like PASE For IBM i After All
  • Will Independent IBM i Clouds Survive PowerVS?
  • Now, IBM Is Jacking Up Hardware Maintenance Prices
  • IBM i PTF Guide, Volume 27, Number 24
  • Big Blue Raises IBM i License Transfer Fees, Other Prices
  • Keep The IBM i Youth Movement Going With More Training, Better Tools
  • Remain Begins Migrating DevOps Tools To VS Code
  • IBM Readies LTO-10 Tape Drives And Libraries
  • IBM i PTF Guide, Volume 27, Number 23

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle