You are viewing the documentation for a prerelease version.

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 on a match or no match in the join. Multiple actions can be specified in the same query.

Couchbase Server supports two types of merge clause, which are described in the sections below: ANSI Merge and Lookup Merge.

The ANSI merge clause has much more flexible functionality than its earlier legacy equivalent. Since it is standard compliant and more flexible, we recommend you to use ANSI merge exclusively, where possible.

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, refer to Authorization.

Syntax

merge ::= MERGE INTO ( ansi-merge | lookup-merge ) [ limit-clause ] [ returning-clause ]
'MERGE' 'INTO' ( ansi-merge | lookup-merge ) limit-clause? returning-clause?

ANSI Merge

(Introduced in Couchbase Server 6.5)

merge-target use-clause 'USING' ansi-merge-source ansi-merge-predicate ansi-merge-actions

ANSI Merge Target

merge-target ::= keyspace-ref [ [ AS ] alias ]
( namespace ':' )? keyspace ( 'AS'? alias )?

The merge target is the keyspace which you want to update, insert into or delete from.

Keyspace Reference

keyspace-ref ::= [ namespace ':' ] keyspace
from keyspace ref

Keyspace reference for the merge target. For more details, refer to Keyspace Reference.

namespace

(Optional) The name or identifier of the namespace of the merge target.

keyspace

(Required) The name or identifier of the keyspace of the merge target.

AS Alias

Assigns another name to the keyspace reference. For details, refer to AS Clause.

alias

String to assign an alias.

Assigning an alias to the keyspace reference is optional. If you assign an alias to the keyspace reference, the AS keyword may be omitted.

ANSI Merge Target Hint

You can use a USE INDEX hint on the merge target to specify that the merge should use a particular index. For details, refer to USE INDEX Clause.

The USE INDEX hint is the only hint allowed on the target. You cannot specify a USE KEYS hint or a join hint (USE NL or USE HASH) on the target of a merge statement.

ANSI Merge Source

( merge-source-keyspace | merge-source-subquery | expr ( 'AS'? alias )? ) ansi-merge-source-hints?

The merge source is the recordset that you want to merge with the merge target. It can be a keyspace reference, a subquery, or a generic expression.

ANSI Merge Keyspace

merge-source-keyspace ::= keyspace-ref [ [ AS ] alias ]
( namespace ':' )? keyspace ( 'AS'? alias )?
Keyspace Reference
keyspace-ref ::= [ namespace ':' ] keyspace
from keyspace ref

Keyspace reference for the merge source. For details, refer to Keyspace Reference.

namespace

(Optional) The name or identifier of the namespace of the merge source.

keyspace

(Required) The name or identifier of the keyspace of the merge source.

AS Alias

Assigns another name to the keyspace reference. For details, refer to AS Clause.

alias

String to assign an alias.

Assigning an alias to the keyspace reference is optional. If you assign an alias to the keyspace reference, the AS keyword may be omitted.

ANSI Merge Subquery

merge-source-subquery ::= subquery-expr [ AS ] alias
subquery-expr 'AS'? alias
Subquery Expression
subquery-expr ::= '('  select ')'
subquery expr

Use parentheses to specify a subquery for the merge source. For details, refer to Subqueries.

AS Alias

Assigns another name to the subquery. For details, refer to AS Clause.

alias

String to assign an alias.

You must assign an alias to a subquery on the merge source. However, when you assign an alias to the subquery, the AS keyword may be omitted.

ANSI Merge Expression

merge-source-expr ::= expr [ [ AS ] alias ]
expr ( 'AS'? alias )?
expr

A N1QL expression generating JSON documents or objects for the merge source.

AS Alias

Assigns another name to the generic expression. For details, refer to AS Clause.

alias

String to assign an alias.

Assigning an alias to the generic expression is optional. If you assign an alias to the generic expression, the AS keyword may be omitted.

ANSI Merge Source Hints

You can specify ANSI join hints (USE HASH or USE NL) on the source of an ANSI merge. For details, refer to ANSI JOIN Hints.

If the merge source is a keyspace, you can also specify a USE KEYS or USE INDEX hint on the merge source. For details, refer to Multiple Hints.

If the merge action is update or delete, you can specify any of the join methods: USE HASH(BUILD), USE HASH(PROBE), or USE NL.

If the merge action is insert, the only join methods you can specify are USE HASH(PROBE) or USE NL. In this case, if you specify USE HASH(BUILD), the join method will default to USE NL.

The ANSI join hint is optional. If omitted, the default hint is USE NL.

If you are using a nested-loop join, i.e. USE NL is specified or no join hint is specified, the target keyspace reference must have an appropriate secondary index defined for the join to work. If such an index cannot be found an error will be returned.

ANSI Merge Predicate

ansi-merge-predicate ::= ON expr
'ON' expr

The merge predicate enables you to specify an ANSI join between the merge source and the merge target.

expr

Boolean expression representing the join condition. This expression may contain fields, constant expressions, or any complex N1QL expression.

ANSI Merge Actions

ansi-merge-actions ::= [ merge-update ] [ merge-delete ] [ ansi-merge-insert ]
merge-update? merge-delete? ansi-merge-insert?

The merge actions enable you to specify insert, update, and delete actions on the target keyspace, based on a match or no match in the join.

ANSI Merge Update

merge-update ::= WHEN MATCHED THEN UPDATE [ set-clause ] ] [ unset-clause ] [ where-clause ]
'WHEN' 'MATCHED' 'THEN' 'UPDATE' set-clause? unset-clause? where-clause?

Updates a document that already exists with updated values.

SET Clause
set-clause ::= SET path '=' expr [ update-for ] [ ',' path '=' expr [ update-for ] ]*
'SET' path '=' expr update-for? (',' path '=' expr update-for?)*
update-for ::= ( FOR [ name-var ':' ] var ( IN | WITHIN ) path [ ',' [ name-var ':' ] var ( IN | WITHIN ) path ]* )+ [ WHEN cond ] END
('FOR' (name-var ':')? var ('IN' | 'WITHIN') path (',' (name-var ':')? var ('IN' | 'WITHIN') path)*)+ ('WHEN' cond)? 'END'
path::= identifier [ '[' expr ']' ]* [ '.' path ]
'identifier ('[' expr ']')* ( '.' path )?

Specifies the value for an attribute to be changed. For more details, refer to SET Clause.

UNSET Clause
unset-clause ::= UNSET path [ update-for ] [ ',' path [ update-for ] ]*
'UNSET' path update-for? (',' path update-for?)*

Removes a specified attribute from the document. For more details, refer to UNSET Clause.

WHERE Clause
where-clause ::= WHERE cond
where clause

Optionally specifies a condition that must be met for data to be updated. For more details, refer to WHERE Clause.

ANSI Merge Delete

merge-delete ::= WHEN MATCHED THEN DELETE [ where-clause ]
'WHEN' 'MATCHED' 'THEN' 'DELETE' where-clause?

Removes the specified document from the keyspace.

WHERE Clause
where-clause ::= WHERE cond
where clause

Optionally specifies a condition that must be met for data to be deleted. For more details, refer to WHERE Clause.

ANSI Merge Insert

ansi-merge-insert ::= WHEN NOT MATCHED THEN INSERT '(' key-clause ',' value-clause ')' [ where-clause ]
'WHEN' 'NOT' 'MATCHED' 'THEN' 'INSERT' '(' key-clause ',' 'VALUE'? expr ')' where-clause?

Inserts a new document into the keyspace. Use parentheses to specify the key and value for the inserted document, separated by a comma.

KEY Clause
key-clause ::= [ KEY ] expr
'KEY'? expr
expr

An expression specifying the key for the inserted document.

The KEY keyword may be omitted. If it is omitted, the VALUE keyword must be omitted also.

Use the UUID() function to generate a random, unique document key.
VALUE Clause
value-clause ::= [ VALUE ] expr
'VALUE'? expr
expr

An expression specifying the value for the inserted document.

The VALUE keyword may be omitted. If it is omitted, the KEY keyword must be omitted also.

WHERE Clause
where-clause ::= WHERE cond
where clause

Optionally specifies a condition that must be met for data to be inserted. For more details, refer to WHERE clause.

Lookup Merge

merge-target 'USING' lookup-merge-source lookup-merge-predicate lookup-merge-actions

Lookup Merge Target

Keyspace reference for the merge target. The syntax is the same as for an ANSI merge. Refer to ANSI Merge Target.

Lookup Merge Source

from-keyspace ('AS'? alias)? use-clause? | '(' select ')' 'AS'? alias | expr ('AS'? alias)?

The merge source is the recordset that you want to merge with the merge target. It can be a keyspace reference, a subquery, or a generic expression.

Lookup Merge Keyspace

Keyspace reference for the merge source. The syntax is the same as for an ANSI merge. Refer to ANSI Merge Keyspace.

Lookup Merge Source Hint

If the merge source is a keyspace, you can specify a USE KEYS or USE INDEX hint on the merge source. For details, refer to USE clause.

Lookup Merge Subquery

Specifies a subquery for the merge source. The syntax is the same as for an ANSI merge. Refer to ANSI Merge Subquery.

Lookup Merge Expression

Specifies a generic expression for the merge source. The syntax is the same as for an ANSI merge. Refer to ANSI Merge Expression.

Lookup Merge Predicate

lookup-merge-predicate ::= ON [ PRIMARY ] KEY expr
'ON' 'PRIMARY'? 'KEY' expr

The merge predicate produces a document key for the target of the lookup merge.

expr

[Required] String or expression representing the primary key of the documents for the target keyspace.

Lookup Merge Actions

lookup-merge-actions ::= [ merge-update ] [ merge-delete ] [ lookup-merge-insert ]
merge-update? merge-delete? lookup-merge-insert?

The merge actions enable you to specify insert, update, and delete actions on the target keyspace, based on a match or no match in the join.

Lookup Merge Update

Updates a document that already exists with updated values. The syntax is the same as for an ANSI merge. Refer to ANSI Merge Update.

Lookup Merge Delete

Removes the specified document from the keyspace. The syntax is the same as for an ANSI merge. Refer to ANSI Merge Delete for details.

Lookup Merge Insert

lookup-merge-insert ::= WHEN NOT MATCHED THEN INSERT expr [ where-clause ]
'WHEN' 'NOT' 'MATCHED' 'THEN' 'INSERT' expr where-clause?

Inserts a new document into the keyspace. The key specified in the Lookup Merge Predicate is used as the key for the newly inserted document.

expr

An expression specifying the value for the inserted document.

WHERE Clause
where-clause ::= WHERE cond
where clause

Optionally specifies a condition that must be met for data to be inserted. For more details, refer to WHERE clause.

Common Clauses

The following clauses are common to both ANSI Merge and Lookup Merge.

LIMIT Clause

limit-clause ::= LIMIT expr
limit clause

Specifies the minimum number of records to be processed. For more details, refer to LIMIT Clause.

RETURNING Clause

returning-clause ::= RETURNING ( result-expr [ ',' result-expr ]* | ( RAW | ELEMENT | VALUE ) expr )
'RETURNING' (result-expr (',' result-expr)* | ('RAW' | 'ELEMENT' | 'VALUE') expr)

Specifies the information to be returned by the operation as a query result. For more details, refer to RETURNING Clause.

Examples

Example 1. ANSI merge with expression source

This example updates the vacancy field based on the source expression.

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

This example finds all BA routes whose source airport is in France. If any flights are using equipment 319, they are updated to use 797.

MERGE INTO `travel-sample` AS route
USING `travel-sample` AS airport
ON route.sourceairport = airport.faa
WHEN MATCHED THEN
  UPDATE SET route.old_equipment = route.equipment,
             route.equipment = "797",
             route.updated = true
WHERE airport.country = "France"
  AND route.airline = "BA"
  AND CONTAINS(route.equipment, "319");
Example 3. ANSI merge with updates and inserts

This example compares a source set of airport data with the travel-sample airport data. If the airport already exists in travel-sample, the record is updated. If the airport does not exist in travel-sample, a new record is created.

MERGE INTO `travel-sample` AS target
USING [
  {"iata":"DSA", "name": "Doncaster Sheffield Airport"},
  {"iata":"VLY", "name": "Anglesey Airport / Maes Awyr Môn"}
] AS source
ON target.faa = source.iata
WHEN MATCHED THEN
  UPDATE SET target.old_name = target.airportname,
             target.airportname = source.name,
             target.updated = true
WHEN NOT MATCHED THEN
  INSERT (KEY UUID(),
          VALUE {"faa": source.iata,
                 "airportname": source.name,
                 "type": "airport",
                 "inserted": true} );
Example 4. Lookup merge with expression source

Lookup merge version of Example 1.

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

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;
Example 6. Lookup merge with updates and inserts

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 };