A newer version of this documentation is available.

View Latest

UPDATE

    +
    UPDATE replaces a document that already exists with updated values.

    Prerequisites

    RBAC Privileges

    User executing the UPDATE statement must have the Query Update privilege on the target keyspace. If the statement has any clauses that needs data read, such as SELECT clause, or RETURNING clause, then 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 privilege on `travel-sample`.

      UPDATE `travel-sample` SET foo = 5
    • To execute the following statement, user must have the Query Update privilege on the `travel-sample` and Query Select privilege on `beer-sample`.

      UPDATE `travel-sample`
      SET foo = 9
      WHERE city IN (SELECT RAW city FROM `beer-sample` WHERE type = "brewery")
    • To execute the following statement, user must have the Query Update privilege on `travel-sample` and Query Select privilege on `travel-sample`.

      UPDATE `travel-sample`
      SET city = "San Francisco"
      WHERE lower(city) = "sanfrancisco"
      RETURNING *

    Syntax

    'UPDATE' update-target update-hint? set-clause? unset-clause? where-clause? limit-clause? returning-clause?

    Update Target

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

    The update target is the keyspace which you want to update.

    Keyspace Reference

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

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

    namespace

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

    keyspace

    (Required) The name or identifier of the keyspace of the update 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.

    Update Hint

    You can use a USE KEYS hint on the update target to specify the keys of the data items to be updated. For details, refer to USE KEYS Clause.

    SET Clause

    set-clause ::= SET path '=' expr [ update-for ] [ ',' path '=' expr [ update-for ] ]*
    'SET' path '=' expr update-for? (',' path '=' expr update-for?)*

    Specifies the value for an attribute to be changed. The value may be a generic expression term, a subquery, or an expression that resolves to nested array elements.

    UNSET Clause

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

    Removes the specified attribute from the document.

    FOR Clause

    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 )?

    Uses the FOR statement to iterate over a nested array to SET or UNSET the given attribute for every matching element in the array. The FOR clause can evaluate functions and expressions, and the UPDATE statement supports multiple nested FOR expressions to access and update fields in nested arrays. Additional array levels are supported by chaining the FOR clauses.

    WHERE Clause

    where-clause ::= WHERE cond
    where clause

    Specifies the condition that needs to be met for data to be updated. Optional.

    LIMIT Clause

    limit-clause ::= LIMIT expr
    limit clause

    Specifies the greatest number of objects that can be updated. This clause must have a non-negative integer as its upper bound. Optional.

    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

    For some of these examples, the Query Workbench may warn you that the query has no WHERE clause and will update all documents. In this case, you can ignore the warning: the USE KEYS hint in these examples ensures that the query updates only one document.
    Example 1. Set an attribute

    The following statement sets the nickname of the landmark "Tradeston Pedestrian Bridge" to "Squiggly Bridge".

    UPDATE `travel-sample` USE KEYS "landmark_10090" SET nickname = "Squiggly Bridge" RETURNING `travel-sample`.nickname;
    [
      {
        "nickname": "Squiggly Bridge"
      }
    ]
    Example 2. Unset an attribute

    This statement removes the nickname attribute from the travel-sample keyspace for the document with the key landmark_10090.

    UPDATE `travel-sample` USE KEYS "landmark_10090" UNSET nickname RETURNING `travel-sample`.name;
    [
      {
        "name": "Tradeston Pedestrian Bridge"
      }
    ]
    Example 3. Set attributes in an array

    This statement sets the codeshare attribute for each element in the schedule array for document route_10003 in the travel-sample keyspace.

    UPDATE `travel-sample` t USE KEYS "route_10003" SET s.codeshare = NULL FOR s IN schedule END RETURNING t;
    [
        {
            "t": {
                "airline": "AF",
                "airlineid": "airline_137",
                "destinationairport": "ATL",
                "distance": 654.9546621929924,
                "equipment": "757 739",
                "id": 10003,
                "schedule": [
                    {
                        "codeshare": null,
                        "day": 0,
                        "flight": "AF986",
                        "utc": "22:26:00"
                    },
                    ...
                    {
                        "codeshare": null,
                        "day": 6,
                        "flight": "AF540",
                        "utc": "11:07:00"
                    }
                ],
                "sourceairport": "TPA",
                "stops": 0,
                "type": "route"
            }
        }
    ]
    Example 4. Set nested array elements
    UPDATE `travel-sample` AS h USE KEYS "hotel_10025"
    SET i.ratings = OBJECT_ADD(i.ratings, "new", "new_value" ) FOR i IN reviews END
    RETURNING h.reviews[*].ratings;
    [
      {
        "ratings": [
          {
            "Cleanliness": 5,
            "Location": 4,
            "Overall": 4,
            "Rooms": 3,
            "Service": 5,
            "Value": 4,
            "new": "new_value"
          },
          {
            "Business service (e.g., internet access)": 4,
            "Check in / front desk": 4,
            "Cleanliness": 4,
            "Location": 4,
            "Overall": 4,
            "Rooms": 3,
            "Service": 3,
            "Value": 5,
            "new": "new_value"
          }
        ]
      }
    ]
    Example 5. Access nested arrays
    Query
    UPDATE `travel-sample` AS h USE KEYS "hotel_10025"
    UNSET i.new FOR i IN
      (ARRAY j.ratings FOR j IN reviews END)
    END
    RETURNING h.reviews[*].ratings;
    Result
    [
      {
        "ratings": [
          {
            "Cleanliness": 5,
            "Location": 4,
            "Overall": 4,
            "Rooms": 3,
            "Service": 5,
            "Value": 4
          },
          {
            "Business service (e.g., internet access)": 4,
            "Check in / front desk": 4,
            "Cleanliness": 4,
            "Location": 4,
            "Overall": 4,
            "Rooms": 3,
            "Service": 3,
            "Value": 5
          }
        ]
      }
    ]
    Example 6. Update a document with the results of a subquery
    Query
    UPDATE `travel-sample` AS a
    SET hotels =
      (SELECT  h.name, h.id
      FROM  `travel-sample` AS h
      WHERE h.type = "hotel" AND h.city = "Nice")
    WHERE a.faa ="NCE"
    RETURNING a;
    Result
    [
      {
        "a": {
          "airportname": "Cote D\\'Azur",
          "city": "Nice",
          "country": "France",
          "faa": "NCE",
          "geo": {
            "alt": 12,
            "lat": 43.658411,
            "lon": 7.215872
          },
          "hotels": [
            {
              "id": 20419,
              "name": "Best Western Hotel Riviera Nice"
            },
            ...
            {
              "id": 20421,
              "name": "NH Nice"
            }
          ],
          "icao": "LFMN",
          "id": 1354,
          "type": "airport",
          "tz": "Europe/Paris"
        }
      }
    ]