A newer version of this documentation is available.

View Latest

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

    merge-source:

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

    keys-clause:

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

    merge-actions:

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

    merge-update:

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

    merge-delete:

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

    merge-insert:

    WHEN NOT MATCHED THEN INSERT expression [where-clause]
    merge insert
    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 }