MERGE

MERGE

A MERGE statement provides the ability to update, insert into, or delete from a keyspace based on the results of a join with another keyspace or subquery. It is possible to specify actions (insert, update, delete) on the keyspace based a match or no match in the join. Multiple actions can be specified in the same query.

merge:

MERGE INTO keyspace-ref USING merge-source ON key-clause merge-actions [limit-clause] [returning-clause]   
 

merge-source:

[from-path] ( [AS] [alias] | ( select ) [AS] alias

keys-clause:

[PRIMARY] KEY expression

merge-actions:

 [merge-update] [merge-delete] [merge-insert]

merge-update:

WHEN MATCHED THEN UPDATE [set-clause] [unset-clause] [where-clause]

merge-delete:

WHEN MATCHED THEN DELETE [where-clause]

merge-insert:

WHEN NOT MATCHED THEN INSERT expression [where-clause]

Examples:

The following statement updates product based on orders.

MERGE INTO product p USING orders o ON KEY o.productId
WHEN MATCHED THEN
     UPDATE SET p.lastSaleDate = o.orderDate
WHEN MATCHED THEN
     DELETE WHERE p.inventoryCount  <= 0

The following statement merges two datasets containing employee information. It then updates all_empts on match with emps_deptb and inserts when there is no match.

MERGE INTO all_empts a USING emps_deptb b ON KEY b.empId
WHEN MATCHED THEN
     UPDATE SET a.depts = a.depts + 1
     a.title = b.title || ", " || b.title
WHEN NOT MATCHED THEN
     INSERT  { "name": b.name, "title": b.title, "depts": b.depts, "empId": b.empId, "dob": b.dob }