The MERGE statement modifies a foreign table using the results of a query . This query can be relational (from a table) or streaming (from a stream): any SELECT or select stream query will work. The foreign table is called the “target” of the merge and the query is called the “source”.
The MERGE statement is like a combination of an INSERT and an UPDATE, along with a JOIN condition. The JOIN condition defines how each source row matches one target row. When there is a match, the matching target row is changed according to the UPDATE part of the merge. When there is no match, the INSERT part of the merge is used to add a new row to the target table.
You can have several source rows match the same target row. (This differs from the SQL standard.) If the source stream contains several rows with the same values in the ON condition, they will match the same target row, and as each source row arrives it will update the target row with new values.
(There is no way to delete a target row. s-Server implements the standard the SQL standard MERGE statement, except for the DELETE clause).
The merge runs til the end of data of its source, so if the source is a streaming query, the merge will run forever. A merge like this is generally run as a pump.
In a stream, ROWTIME is a special column: every row has a rowtime. In a table however, there is no special rowtime column. A table may contain an ordinary column named ROWTIME of type TIMESTAMP, or it may not, according to its definition (see CREATE TABLE). A merge statement can save a value as the ROWTIME of a target row, but it must do so explicitly in the UPDATE and INSERT clauses.
MERGE INTO <target table> <options> [ AS <targetAlias> ] USING <source query> ON <conditions> WHEN MATCHED THEN <merge update> WHEN NOT MATCHED THEN <merge insert>; where: <conditions> = a boolean expression in columns from the target and the source row <merge update> = UPDATE SET <column1> = <expr1>, ... <columnN> = <exprN> <merge insert> = INSERT (<column1>,...<columnN>) VALUES (<expr1>,...<exprN>) and <expr> is any scalar expression based on columns from the target and the source row. <options> ::= WITH OPTIONS( <option> [, <option>]* ) <option> ::= <optionName> <optionValue> <optionName> ::= BULK_LOAD | TRANSACTION_ROWTIME_LIMIT | TRANSACTION_ROW_LIMIT | PRESERVE_PARTIAL_WRITES | RETRY_COUNT | RETRY_MAX_TIME | RETRY_PAUSE_MIN | RETRY_PAUSE_MAX <optionValue> ::= string constant
Options are defined below.
The INTO clause indicates the target table or foreign table to be updated.
The USING clause indicates the source of the data to be updated or inserted. The source can be a table, foreign table, or the result of a subquery.
The ON clause indicates the condition for the MERGE statement updating or inserting rows. When the search condition is met, s-Server updates the target table row with corresponding data from the source. If the condition is not true for any rows, then s-Server inserts into target table based on the corresponding source row.
The WHEN MATCHED THEN clause indicates new column values for the target table. s-Server updates columns if the ON clause is true.
The WHEN NOT MATCHED THEN clause indicates columns and values for rows to be inserted if the ON clause is false.
Guavus SQLstream 6.0 introduces DML options for INSERT and MERGE. These options override stream/table and server options. Currently available options are as follows:
|BULK_LOAD||DML option on INSERT/MERGE only||You can use this option for insert/merges with tables as sources only–a type of statement known as “finite DML”. This is a Boolean operator. When this option is set to “true”, s-Server will commit rows whenever a batch of buffered rows is flushed to the external database. The statement will fail if the target database raises an exception. In that case, the failing batch will be rolled back but previous batches will remain committed. Note: infinite DML always commits on batch boundaries.|
|PRESERVE_PARTIAL_WRITES||DML option on INSERT/MERGE only||True or false. Defaults to false. If true, when a row fails to write to an RDBMS table, the failed row is discarded, but the statement continues to run.This will allow other rows to be written to the RDBMS table, depending on how the database’s JDBC driver handles java.sql.Statement.executeBatch().|
|TRANSACTION_ROWTIME_LIMIT||DML option on INSERT/MERGE, table, server options||You can use this option for insert/merges with streams as sources only–a type of statement known as “infinite DML”. This is the number of milliseconds to collect rows before committing.|
|TRANSACTION_ROW_LIMIT||DML option on INSERT/MERGE, table, server options||This is the number of rows to collect before committing.|
|RETRY_COUNT||DML option on INSERT/MERGE only||The number of times that s-Server tries rewriting a batch of rows that fails because of a transient error. -1 means “no maximum number of retries”. Other negative values (and non-integer values) are illegal. For finite DML, RETRY_COUNT defaults to 0. For infinite DML, it defaults to -1.Each retry attempt will pause longer than the previous attempt.|
|RETRY_MAX_TIME||DML option on INSERT/MERGE only||The maximum number of milliseconds that s-Server will spend retrying a failed batch. It defaults to -1, which means “no maximum time”. RETRY_MAX_TIME = 0 means the same thing as RETRY_COUNT = 0: don’t retry at all.|
|RETRY_PAUSE_MIN||DML option on INSERT/MERGE only||The minimum number of milliseconds to pause between retries. It defaults to 10,000 (10 seconds). The value must be a positive integer–anything else raises an error.|
|RETRY_PAUSE_MAX||DML option on INSERT/MERGE only||The maximum number of milliseconds to pause between retries. It defaults to 300,000 (5 minutes). The value must be a positive integer–anything else raises an error.|
The following example creates a pump implementing a MERGE on a loopback table called “LOOPBACK”.“MOCHI_VIZ”.“CityAttackTotals” using a query. The merge condition asks if the columns state and city match between the target table and the query. When the merge condition is met, the columns state, city, value, and lastmod are updated. When the merge condition is not met, new rows are inserted into the table.
CREATE OR REPLACE PUMP "100-persistCityAttacks" STOPPED DESCRIPTION 'persist city attack totals to local/loopback table' AS MERGE INTO "LOOPBACK"."MOCHI_VIZ"."CityAttackTotals" AS "CAT" --query: ```sql USING (SELECT STREAM COALESCE("region", '*') AS "state", "city", "totalFails", CURRENT_ROW_TIMESTAMP AS "lastmod" FROM "MOCHI"."SuspectLoginLocations") AS "SLL" --merge condition: ```sql ON ("CAT"."state" = "SLL"."state") AND ("CAT"."city" = "SLL"."city") --merge update specification: ```sql WHEN MATCHED THEN UPDATE SET "state" = "SLL"."state", "city" = "SLL"."city", "value" = "SLL"."totalFails", "lastmod" = "SLL"."lastmod" --merge insert specification: ```sql WHEN NOT MATCHED THEN INSERT ("state", "city", "value", "lastmod") VALUES ("SLL"."state", "SLL"."city", "SLL"."totalFails", "SLL"."lastmod");