The merge statement will be supported in the new version of Sybase ASE V15.7

In September 2011, Sybase will release the next version of ASE, V15.7. Current ASE Latest version is V15.5, version number V15.6 will be skipped, cause and from V15.0 jump directly to V15.5 or V12.5 to V15.0 is similar. Sometimes this has to be done.

A number of new features are added to the upcoming Sybase ASE V15.7. This blog will focus on one of the more practical features: the merge command.

The first reference to the merge command in the ANSI-SQL2003 standard is simple: "Insert nonexistent records into the target table and update existing data." Other database management systems, such as Oracle, introduced the merge command in 9i, as well as the command in Sybase SQL anywhere. To date, the functionality of the merge can only be implemented using a similar if update else insert method in Sybase ASE.

Here is an example of using merge. Table Customers stores customer information, and you need to update the data in the table Cust_updates_week_10 to the Customer table customers each week. For simplicity's sake, this example only updates the 2 fields of address and number.

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 then
Update set ADDR=U.ADDR, Phone=u.phone

Let's analyze the SQL statement above. Line 1th Merge into Customers as C indicates Customers as the destination table. The 2nd row using Cust_updates_week_10 as U indicates that the cust_updates_week_10 is the source datasheet, and that the inserted or updated content will come from this table. Line 3rd Specifies the condition that the data row exists, and this example specifies only one condition for the demonstration. On c.custid = U.custid will enable the ASE Grammar interpreter to do the left OUTER join processing for customers and CUST_UPDATES_WEEK_10. The 第4-5 line represents data that cust_updates_week_10 contains and does not exist in customers, i.e. "new customer" data, which is inserted into the table customers. Line 6th indicates that the Non-key column can be updated when the records in the two tables match. This example only updates address and phone two fields.

We can expand the syntax of the merge command, for example: You can use multiple statements such as "when present", "when not present". The following example uses the two "when matched" statement, and the first "when matched" statement determines whether the address field in the existing record is empty, and when the address is empty, the record is deleted.

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: The order of the statement "when matched" and "when not matched" affects the results of the processing. In the SQL statement above, when the condition of the first statement is fully satisfied, the following similar "when [not] matched" statement is not executed.

The MERGE command is more practical where it integrates more complex statements into a single SQL statement. Not only makes the SQL statement more concise, clear, but also significantly improve the efficiency of execution. Use the merge command to access the contents of the source data table only once, and then handle each record directly. If you use a separate insert, UPDATE, and DELETE statement to implement similar functionality, you will access the contents of the Source data table at least three times.

The merge command is more flexible, and you can use derived tables in addition to base tables.

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

The merge command has some limitations, such as in the above SQL statement, the key column (CustID) in line 3rd cannot be updated. Of course, there are other restrictions. Let's look forward to the release of the ASE V15.7 version.

This article is from: http://blogs.sybase.com/database/2011/05/peeking-ahead-to-ase-15-7-the-merge-statement/