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 }