UPDATE

  • reference
    +
    UPDATE replaces a document that already exists with updated values.
    Please note that the examples on this page will alter the data in your sample buckets. To restore your sample data, remove and reinstall the travel-sample bucket. Refer to Sample Buckets for details.

    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.

    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.
    RBAC Examples

    For this example, set the query context to the inventory scope in the travel sample dataset. For more information, see Query Context.

    To execute the following statement, you must have the Query Update privilege on airport.

    UPDATE airport SET foo = 5;

    To execute the following statement, you must have the Query Update privilege on airport and Query Select privilege on `beer-sample`.

    UPDATE airport
    SET foo = 9
    WHERE city IN (SELECT RAW city FROM `beer-sample` WHERE type = "brewery");

    To execute the following statement, you must have the Query Update and Query Select privileges on airport.

    UPDATE airport
    SET city = "San Francisco"
    WHERE lower(city) = "san francisco"
    RETURNING *;

    Syntax

    update ::= 'UPDATE' target-keyspace use-keys? set-clause? unset-clause?
                where-clause? limit-clause? returning-clause?
    Syntax diagram: refer to source code listing
    target-keyspace

    Update Target

    use-keys

    Update Hint

    set-clause

    SET Clause

    unset-clause

    UNSET Clause

    where-clause

    WHERE Clause

    limit-clause

    LIMIT Clause

    returning-clause

    RETURNING Clause

    Update Target

    target-keyspace ::= keyspace-ref ( 'AS'? alias )?
    Syntax diagram: refer to source code listing

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

    keyspace-ref

    Keyspace Reference

    alias

    AS Alias

    Keyspace Reference

    keyspace-ref ::= keyspace-path | keyspace-partial
    Syntax diagram: refer to source code listing
    keyspace-path ::= ( namespace ':' )? bucket ( '.' scope '.' collection )?
    Syntax diagram: refer to source code listing
    keyspace-partial ::= collection
    Syntax diagram: refer to source code listing

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

    AS Alias

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

    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' ( meta '=' expiration | path '=' expr update-for? )
                   ( ',' ( meta '=' expiration | path '=' expr update-for? ) )*
    Syntax diagram: refer to source code listing

    Specifies the value for an attribute to be changed.

    path

    A path specifying the attribute to be changed.

    expr

    The value may be a generic expression term, a subquery, or an expression that resolves to nested array elements.

    update-for

    FOR Clause

    The SET clause also supports alternative arguments which enable you to set the expiration of the document.

    meta

    A META().expiration expression specifying the expiration property of the document being updated.

    expiration

    An integer, or an expression resolving to an integer, representing the document expiration in seconds.

    If the document expiration is not specified, the document expiration is set according to the request-level preserve_expiration parameter. If this is true, the existing document expiration is preserved; if false, the document expiration defaults to 0, meaning the document expiration is the same as the bucket or collection expiration.

    UNSET Clause

    unset-clause ::= 'UNSET' path update-for? (',' path update-for?)*
    Syntax diagram: refer to source code listing

    Removes the specified attribute from the document.

    path

    A path specifying the attribute to be removed.

    update-for

    FOR Clause

    You cannot use the UNSET clause to unset the document expiration. To unset the document expiration, set the document expiration to 0. Alternatively, if the request-level preserve_expiration parameter is set to false, simply update the document without specifying the document expiration.

    FOR Clause

    update-for ::= ('FOR' (name-var ':')? var ('IN' | 'WITHIN') path
                   (','   (name-var ':')? var ('IN' | 'WITHIN') path)* )+
                   ('WHEN' cond)? 'END'
    Syntax diagram: refer to source code listing
    path ::= identifier ( '[' expr ']' )* ( '.' identifier ( '[' expr ']' )* )*
    Syntax diagram: refer to source code listing

    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
    Syntax diagram: refer to source code listing

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

    LIMIT Clause

    limit-clause ::= 'LIMIT' expr
    Syntax diagram: refer to source code listing

    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)
    Syntax diagram: refer to source code listing

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

    Examples

    To try the examples in this section, set the query context to the inventory scope in the travel sample dataset. For more information, see Query Context.

    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 landmark
    USE KEYS "landmark_10090"
    SET nickname = "Squiggly Bridge"
    RETURNING landmark.nickname;
    [
      {
        "nickname": "Squiggly Bridge"
      }
    ]
    Example 2. Unset an attribute

    This statement removes the nickname attribute from the landmark keyspace for the document with the key landmark_10090.

    UPDATE landmark
    USE KEYS "landmark_10090"
    UNSET nickname
    RETURNING landmark.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 route keyspace.

    UPDATE route 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": 0,
              "flight": "AF962",
              "utc": "04:25:00"
            },
    // ...
          ],
          "sourceairport": "TPA",
          "stops": 0,
          "type": "route"
        }
      }
    ]
    Example 4. Set nested array elements
    UPDATE hotel 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 hotel 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 airport AS a
    SET hotels =
      (SELECT  h.name, h.id
      FROM  hotel AS h
      WHERE 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": 20420,
              "name": "Hotel Anis"
            },
            {
              "id": 20421,
              "name": "NH Nice"
            },
            {
              "id": 20422,
              "name": "Hotel Suisse"
            },
            {
              "id": 20423,
              "name": "Gounod"
            },
            {
              "id": 20424,
              "name": "Grimaldi Hotel Nice"
            },
            {
              "id": 20425,
              "name": "Negresco"
            }
          ],
          "icao": "LFMN",
          "id": 1354,
          "type": "airport",
          "tz": "Europe/Paris"
        }
      }
    ]
    Example 7. Update a document and set expiration

    Update a document and set the expiration to 1 week.

    Query
    UPDATE route t USE KEYS "route_10003"
    SET meta(t).expiration = 7*24*60*60,
    s.codeshare = NULL FOR s IN schedule END;
    Example 8. Update a document and preserve expiration
    Query
    UPDATE route t USE KEYS "route_10003"
    SET meta(t).expiration = meta(t).expiration,
    s.codeshare = NULL FOR s IN schedule END;

    Note that it is possible to preserve the document expiration using the request-level preserve_expiration parameter.

    Example 9. Update a document and unset expiration

    Set the document expiration to 0 to unset the document expiration. (In this case, the document expiration defaults to be the same as the bucket or collection expiration.)

    Query
    UPDATE route t USE KEYS "route_10003"
    SET meta(t).expiration = 0,
    s.codeshare = NULL FOR s IN schedule END;

    Alternatively, if the request-level preserve_expiration parameter is set to false, and you update the document without specifying the document expiration, the document expiration defaults to 0.

    Query
    UPDATE route t USE KEYS "route_10003"
    SET s.codeshare = NULL FOR s IN schedule END;