The MERGE statement modifies a foreign table using the results of a query. Query can be relational or streaming: any SELECT or SELECT STREAM query will work. The 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.
It is allowed for several source rows to match the same target row. This differs from the SQL:2008 standard, but makes more sense when the source is a stream. 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 SQL:2008 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.
MERGE and Rowtime
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> USING <source query> ON <conditions>
WHEN MATCHED THEN <merge update>
WHEN NOT MATCHED THEN <merge insert>;
<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.
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.
WHEN MATCHED THEN
The WHEN MATCHED THEN clause indicates new column values for the target table. s-Server updates columns if the ON clause is true.
WHEN NOT MATCHED THEN
The WHEN NOT MATCHED THEN clause indicates columns and values for rows to be inserted if the ON clause is false.
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'
MERGE INTO "LOOPBACK"."MOCHI_VIZ"."CityAttackTotals" AS "CAT"
COALESCE("region", '**') AS "state",
"city", "totalFails", CURRENT_ROW_TIMESTAMP AS "lastmod"
FROM "MOCHI"."SuspectLoginLocations") AS "SLL"
ON ("CAT"."state" = "SLL"."state") AND ("CAT"."city" = "SLL"."city")
--merge update specification:
WHEN MATCHED THEN
"state" = "SLL"."state",
"city" = "SLL"."city",
"value" = "SLL"."totalFails",
"lastmod" = "SLL"."lastmod"
--merge insert specification:
WHEN NOT MATCHED THEN
INSERT ("state", "city", "value", "lastmod")
VALUES ("SLL"."state", "SLL"."city", "SLL"."totalFails", "SLL"."lastmod");