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.

    Prerequisites

    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`
    A user with the Data Writer privilege may set documents to expire. When the document expires, the data service deletes the document, even though the user may not have the Query Delete privilege.

    Syntax

    upsert ::= UPSERT INTO insert-target ( insert-values | insert-select ) [ returning-clause ]
    'UPSERT' 'INTO' insert-target ( insert-values | insert-select ) returning-clause?

    Insert Target

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

    Specifies the keyspace into which to upsert documents.

    Keyspace Reference

    keyspace-ref ::= [ namespace ':' ] keyspace
    ( namespace ':' )? keyspace

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

    namespace

    (Optional) An identifier that refers to the namespace of the insert target. Currently, only the default namespace is available. If the namespace name is omitted, the default namespace in the current session is used.

    keyspace

    (Required) An identifier that refers to the bucket name or keyspace of the insert target.

    For example, default:`travel-sample` indicates the travel-sample keyspace in the default namespace.

    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.

    Insert Values

    insert-values ::= [ "(" [ PRIMARY ] KEY "," VALUE [ "," OPTIONS ] ")" ] values-clause
    ( '(' 'PRIMARY'? 'KEY' ',' 'VALUE' ( ',' 'OPTIONS' )? ')' )? values-clause

    Specifies one or more documents to be upserted using the VALUES clause. For details, refer to Insert Values.

    VALUES Clause

    values-clause ::= VALUES "(" key "," value [ "," options ] ")" [ "," [ VALUES ] "(" key "," value [ "," options ] ")" ]*
    'VALUES' '(' key ',' value ( ',' options )? ')' ( ',' 'VALUES'? '(' key ',' value ( ',' options )? ')' )*

    Specify the values as well-formed JSON. Also enables you to set the expiration of the upserted documents. For details, refer to VALUES Clause.

    Insert Select

    insert-select ::= "(" [ PRIMARY ] KEY key [ "," VALUE value ] [ "," OPTIONS options ] ")" select
    '(' 'PRIMARY'? 'KEY' key ( ',' 'VALUE' value )? ( ',' 'OPTIONS' options )? ')' select

    Specifies the documents to be upserted as a SELECT statement. Also enables you to set the expiration of the upserted documents. For details, refer to Insert Select.

    SELECT Statement

    SELECT statements let you retrieve data from specified keyspaces. For details, refer to SELECT Syntax.

    RETURNING Clause

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

    Specifies the fields that must be returned as part of the results object.

    Result Expression

    result-expr ::= ( [ path "." ] "*" | expr [ [ AS ] alias ] )
    ( path '.' )? '*' | expr ( 'AS'? alias )?

    Specifies an expression on the data you upserted, to be returned as output. For details, refer to Result Expression.

    Example

    The following statement upserts documents with type landmark-pub into the travel-sample namespace.

    Query
    UPSERT INTO `travel-sample` (KEY, VALUE)
    VALUES ("upsert-1", { "name": "The Minster Inn", "type": "landmark-pub"}),
    ("upsert-2", {"name": "The Black Swan", "type": "landmark-pub"})
    RETURNING VALUE name;
    Result
    [
      "The Minster Inn",
      "The Black Swan"
    ]