UPSERT

UPSERT

Used to insert a new record or update an existing one. If the document doesn’t exist it will be created. UPSERT is a combination of INSERT and UPDATE.

upsert:

UPSERT INTO keyspace-ref [insert-values | insert-select]  [ returning-clause]

keyspace-ref:

[(namespace-name :)]  keyspace [ [AS] alias]

keyspace:

identifier

insert-values:

 [ (  [ PRIMARY ] KEY , VALUE ) ]  values-clause [, values-clause]* 

values-clause:

VALUES ( expression, expression ) 

insert-select

( [PRIMARY] KEY expression [ , VALUE expression ] ) select 

returning-clause:

 RETURNING (result-expression [, result-expression]* | [RAW | ELEMENT ] expression)

result-expression:

[path . ]  * | expression [ [ AS ] alias ]

path:

identifier [ [ expression ] ] [ . path ]

keyspace-ref: Specifies the keyspace into which to upsert the document.

You can add an optional namespace-name to the keyspace-name in this way:

namespace-name:keyspace-name.

For example, main:customer indicates the customer keyspace in the main namespace. If the namespace name is omitted, the default namespace in the current session is used.

insert-values: Specifies the values to be upserted.

insert-select: Specifies the values to be upserted as a SELECT statement.

returning-clause: Returns the data you upserted as specified in the result_expression.

RBAC Privileges

User executing the UPSERT statement must have the Query Update and Query Insert privileges on the target keyspace. If the statement has any RETURNING clauses, then the Query Select privilege is also required on the keyspaces referred in the respective clauses. For more details about user roles, see Authorization.

For example,

To execute the following statement, user must have the Query Update and Query Insert privileges on `travel-sample`.

UPSERT INTO `travel-sample` (KEY, VALUE)
VALUES ("key1", { "type" : "hotel", "name" : "new hotel" })

To execute the following statement, user must have the Query Update and Query Insert privileges on the `travel-sample` bucket and Query Select privilege on `travel-sample` (for RETURNING clause).

UPSERT INTO `travel-sample` (KEY, VALUE)
VALUES ("key1", { "type" : "hotel", "name" : "new hotel" }) 
RETURNING *

To execute the following statement, user must have the Query Update and Query Insert privileges on the `travel-sample` bucket and Query Select privilege on `beer-sample`.

UPSERT INTO `travel-sample` (KEY foo, VALUE bar) 
SELECT foo, bar FROM `beer-sample`

Example

The following statement upserts values for odwalla-juice1 into the product document:

UPSERT INTO product (KEY, VALUE) VALUES ("odwalla-juice1", { "productId": "odwalla-juice1", 
      "unitPrice": 5.40, "type": "product", "color":"red"}) RETURNING * ;

"results": [
        {
            "color": "red",
            "productId": "odwalla-juice1",
            "type": "product",
            "unitPrice": 5.4
        }
    ]