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]   
 
Figure 1. Railroad Diagram: merge


merge-source:

[from-path] ( [AS] [alias] | ( select ) [AS] alias
Figure 2. Railroad Diagram: merge-source


keys-clause:

[PRIMARY] KEY expression
Figure 3. Railroad Diagram: key-clause


merge-actions:

 [merge-update] [merge-delete] [merge-insert]
Figure 4. Railroad Diagram: merge-actions


merge-update:

WHEN MATCHED THEN UPDATE [set-clause] [unset-clause] [where-clause]
Figure 5. Railroad Diagram: merge-update


merge-delete:

WHEN MATCHED THEN DELETE [where-clause]
Figure 6. Railroad Diagram: merge-delete


merge-insert:

WHEN NOT MATCHED THEN INSERT expression [where-clause]
Figure 7. Railroad Diagram: merge-insert


RBAC Privileges

User executing the MERGE statement must have the following privileges:

  • Query Select privileges on the source keyspace
  • Query Insert, Query Update, or Query Delete privileges on the target keyspace as per the MERGE actions
  • Query Select privileges on the keyspaces referred in the RETURNING clause
For more details about user roles, see Authorization.

For example,

MERGE INTO `travel-sample` t 
USING [{"id":"21728"},{"id":"21730"}] source
ON KEY "hotel_"|| source.id
WHEN MATCHED THEN UPDATE SET t.old_vacancy = t.vacancy, t.vacancy = false 
RETURNING meta(t).id, t.old_vacancy, t.vacancy;

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 }