Skip to Content

(originally published at www.sybase.com in May 2011; moved to SCN in November 2012)

In the second half of 2011, the release of the next version of ASE is expected: ASE 15.7.

(on the version number: the previous version was ASE 15.5, so, indeed, we’re skipping 15.6. Why? Well, because sometimes you just have to…)

I’ve been on a road show last week (the EMEA TechDays) and on a transatlantic flight this week, and that always brings lots of wasted time at airports and on planes.

Putting some of that time to good use got me to dig into the many, many new features that will be part of ASE 15.7.

In this blog I’d just like to put the spotlight on a new feature that I really like: the MERGE SQL statement.

MERGE appeared in the ANSI SQL 2003 standard. Its can be described as “insert rows into a table, except when they already exist in that table, in which case the existing rows should be updated”.

The requirement to implement such functionality occurs more often in database systems, and is often referred to as “upsert”,  i.e. “update+insert” (though, as I’ll explain later, that’s actually not a good term to use).

Here’s an example of using MERGE. We have a central table Customers that contains customer data, and we need to process a weekly set of updates. These updates are rows in the table Cust_updates_week_10 (for simplicity, the updates only concern the address and phone number in this example):

1    merge into Customers as c
2    using Cust_updates_week_10 as u
3    on c.CustID = u.CustID
4    when not matched then
5         insert (CustID,Addr,Phone) values(u.CustID,u.Addr,u.Phone)
6    when matched then
7         update set Addr=u.Addr, Phone=u.Phone

Let’s walk down the various lines:

Lines 1/2 specifies that the target is the table (or view) Customers, and the rows from Cust_updates_week_10 should be inserted or updated here.

Line 3 specifies the condition that determines whether a row exists or not. This example shows just one predicate, but you can specify more. Under the covers, ASE performs a left outer join between Cust_updates_week_10 and Customers, using this on-clause (why an outer join? Because all rows in Cust_updates_week_10 must be processed).

Line 4/5 indicate that when a row from Cust_updates_week_10 does not exist in Customers (according to the on-clause on line 3), then it is apparently a ‘new’ customer and it must be inserted.

If the row does exist already (line 6), then the non-key columns of the existing row must be updated (line 7 — again, this example restricts itself to address and phone number columns only)

The basic idea of the MERGE syntax can be extended. For example, there can be multiple “when matched” and “when not matched” clauses, and these can specify additional predicates.

In the example below, there are two “when matched” clauses, with the first one testing for an empty address — meaning that when the row’s key already exists, but the new address is empty, then the existing row must be deleted from the Customers table:

merge into Customers as c
using Cust_updates_week_10 as u
on c.CustID = u.CustID
when not matched then
  insert (CustID,Addr,Phone) values(u.CustID,u.Addr,u.Phone)
when matched and u.Addr = NULL then
  delete
when matched then
  update set Addr=u.Addr, Phone=u.Phone

Note that the order of multiple “when matched” or “when not matched” clauses is important: for the first clause where all conditions are true, the action is executed, and subsequent clauses of the same “when [not] matched” type, will be ignored.

This last example shows why “upsert” is too restricted a summary of what MERGE can do: not just insert-or-otherwise-update, but it can also delete. So “updelsert” would do more justice to the functionality of MERGE, but somehow that sounds less catch than “upsert”.

The attraction of MERGE is that you can pack quite a bit of functionality into one statement. Not only does that let you write more compact SQL code, but it can also deliver better performance. The MERGE statement makes only one pass over the source table (Cust_updates_week_10), and directly processes every row it finds. If you were to write the same functionality with individual insert, update and delete statements, you would need at least three passes over the data.

MERGE is flexible: instead of using a source table, a derived table can be specified as follows:

merge into Customers as c
using (select CustID, Addr, Phone from Cust_updates_week_10
       where Phone is not NULL) as u
on c.CustID = u.CustID
when not matched then
  insert (CustID,Addr,Phone) values(u.CustID,u.Addr,u.Phone
when matched then
  update set Addr=u.Addr, Phone=u.Phone

Note that there are some restrictions around using MERGE. For example, the key columns on line 3 cannot be updated. There may be some other restrictions too; we’ll see how far things got once 15.7 is released.

Stay tuned for more information about ASE 15.7!

Follow rob_verschoor on Twitter

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Rahul Vidhate

    Hi Rob,

    Thanks for such a descriptive post. I am a SQL Server guy. I just moved to Sybase database. I firstly search for MERGE command in Syabse and I found that it was just introduced in Sybase ASE 15.7.

    I tried this command I found below error as,

    MERGE is not allowed because different MERGE actions are referenced in the same WHEN [NOT] MATCHED clause.

    Msg: 451, Level: 16, State: 1

    My command is,

    MERGE INTO Destination AS dest

        USING Sorce AS src

        ON src.Destination = dest.Destination

            AND src.AcctNo = dest.AcctNo

        WHEN MATCHED AND src.Reason = 3

            THEN DELETE   

        WHEN MATCHED AND src.Reason = 2

            THEN UPDATE SET

                PartyID      = src.PartyID,

                PartyPercent = src.PartyPercent

        WHEN NOT MATCHED AND src.Reason = 1 THEN

            INSERT (Destination, AcctNo, PartyID, PartyPercent)

                VALUES (src.Destination, src.AcctNo, src.PartyID, src.PartyPercent)

    Can you please guide me to solve this issue?

    (0) 
    1. Rob Verschoor Post author

      This blog was written before ASE 15.7 was released and as I mentioned in the last paragraph, it was not fully clear at that point which restrictions would apply in the final release.

      As it happened, one of the restrictions that was imposed after this blog was written was that support for multiple types of “when [not] matched” actions was removed.  Unfortunately, this was not very well documented.

      In your case, I think you need to remove either the update or the delete branch.

      (0) 

Leave a Reply