• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Guru: More About Merge

    November 5, 2018 Ted Holt

    I often read back through articles that have appeared in this august publication to look for errors and omissions. Such an expedition recently made me aware that I have not told you as much as I would like to about the SQL MERGE statement. Today I am pleased to provide more information.

    First I want to be sure that everybody understands is that you can add conditions to the WHEN MATCHED and WHEN NOT MATCHED expressions. That means that you do not have to treat all matched or unmatched rows in the same way. Look at this example:

    merge . . .
    when not matched and src.Action = 1 then
    . . .
    when matched and src.Action = 2 then
    . . .
    when matched and src.Action = 3 then
    . . .
    when matched then
    . . .
    when not matched then
    . . .
    

    Here are five tests: three for matched rows and two for unmatched rows. As with the CASE structure that we often use in SELECT statements, MERGE will execute the operation of the first condition that proves true. The last two tests are “catch-all” tests and execute only when the action column of the source dataset has an invalid value.

    The three periods following each test stand for an operation, and this brings me to the second thing I want to share today.

    You may remember from previous articles that MERGE is a combination of INSERT and UPDATE. Well, it’s more than that. There are four — not two — operations that MERGE can carry out. MERGE can also delete rows and raise error conditions. Here’s the full MERGE statement using all four operations.

    merge into releases as tgt
       using (select * from relupdates) as src
          on (tgt.PONumber = src.PONumber
         and  tgt.LineNumber = src.LineNumber
         and  tgt.ReleaseNumber = src.ReleaseNumber)
    when not matched and src.Action = 1 then
       insert (PONumber, LineNumber, ReleaseNumber,
               ReleaseDate, Quantity)
          values (src.PONumber, src.LineNumber, 
    	          src.ReleaseNumber, src.ReleaseDate,
    			  src.Quantity)
    when matched and src.Action = 2 then
       update set tgt.ReleaseDate = src.ReleaseDate,
                  tgt.Quantity    = src.Quantity
    when matched and src.Action = 3 then
       delete
    when matched then
       signal sqlstate '87501'
          set message_text = 'Error on matched'
    when not matched then
       signal sqlstate '87502'
          set message_text = 'Error on unmatched';
    

    This MERGE statement might be the sort of thing you would use in a purchasing application. The database has a file of blanket purchase order releases:

    create table releases
      (PONumber      dec(5),
       LineNumber    dec(3),
       ReleaseNumber dec(3),
       ReleaseDate   date,
       Quantity      dec(3),
      primary key (PONumber, LineNumber, ReleaseNumber));
    
    Order Line Release Date Quantity
    101 4 1 2018-11-05 12
    101 4 2 2018-11-12 10
    101 4 3 2018-11-19 8
    213 1 1 2018-11-12 6
    213 1 2 2018-11-19 8

    Another table contains a batch of changes to be applied to the releases.

    create table relupdates
      (Sequence      dec(3),
       PONumber      dec(5),
       LineNumber    dec(3),
       ReleaseNumber dec(3),
       Action        dec(1),
       ReleaseDate   date,
       Quantity      dec(3),
      primary key (Sequence));
    

    The ACTION column (field) tells what to do to the release.

    Action Description
    1 Add a release
    2 Change a release
    3 Delete a release

    A batch of transactions would look like this:

    Sequence Order Line Release Action Date Quantity
    1 101 4 3 2 2018-11-24 10
    2 101 4 4 1 2018-11-28 16
    3 213 1 2 3 2018-11-01  0
    4 213 1 7 2 2018-11-30 14

    The first three transactions are valid.

    Transaction 1 updates the date and quantity of an existing release.

    when matched and src.Action = 2 then
       update set tgt.ReleaseDate = src.ReleaseDate,
                  tgt.Quantity    = src.Quantity
    

    Transaction 2 adds a new release.

    when not matched and src.Action = 1 then
       insert (PONumber, LineNumber, ReleaseNumber,
               ReleaseDate, Quantity)
          values (src.PONumber, src.LineNumber, 
    	          src.ReleaseNumber, src.ReleaseDate,
    			  src.Quantity)
    

    Transaction 3 deletes an existing release.

    when matched and src.Action = 3 then
       delete
    

    Transaction 4 is invalid, as there is no release 7 for line 1 of purchase order 213. SIGNAL raises a condition with SQLSTATE 87502.

    when not matched then
       signal sqlstate '87502'
          set message_text = 'Error on unmatched';
    

    MERGE is powerful. The more I use it, the more I like it.

    RELATED STORIES

    A More Efficient Way To Merge With SQL

    The Powerful SQL Upsert

    Updating Through A Join With SQL, Take Three

    Merge Into the Synchronization Fast Lane with DB2 for i 7.1

    IBM Knowledge Center – MERGE

    IBM Knowledge Center – SIGNAL

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags: Tags: 400, DB2 for i, FHG, Four Hundred Guru, IBM i, SQL

    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
    Email: software@worksright.com
    Website: www.worksright.com

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    As I See It: Rethinking Retirement Cloud Provider Connectria In Major Partnership Push

    Leave a Reply Cancel reply

TFH Volume: 28 Issue: 74

This Issue Sponsored By

  • New Generation Software
  • UCG Technologies
  • SEA
  • Computer Keyes
  • Manta Technologies

Table of Contents

  • IBM i In The Land Of The Rising Sun
  • Cloud Provider Connectria In Major Partnership Push
  • Guru: More About Merge
  • As I See It: Rethinking Retirement
  • I Dare You To Keep Track Of Power Systems Memory Prices

Content archive

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

Recent Posts

  • The IBM i Power10 Upgrade Cycle Forecast Looks Favorable
  • White Hats Completely Dismantle Menu-Based Security
  • Cloud Software To Drive Enterprise Application Growth
  • How Do You Stay In Touch With The IBM i Community?
  • IBM i PTF Guide, Volume 25, Number 6
  • Security Still Top Concern, IBM i Marketplace Study Says
  • Bob Langieri Shares IBM i Career Trends Outlook for 2023
  • Kisco Brings Native SMS Messaging to IBM i
  • Four Hundred Monitor, February 1
  • 2023 IBM i Predictions, Part 4

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 © 2022 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.