(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!
2 Comments
