Previous Next JavaScript must be enabled to correctly display this content

## Using SQL command to update data

An update statement can be used to update a row in a table. It now supports updating multiple rows in a table that share the same shard key. You can find which columns in your table comprise of the shard key here.

- The update statement uses update clauses to modify the values of one or more fields in a table. Oracle NoSQL Database supports the following update clauses:
  - SET clause updates the value of one or more existing fields.
  - ADD clause adds new elements in one or more arrays.
  - PUT clause adds new fields in one or more maps. It can also update the values of existing map fields.
  - REMOVE clause removes elements/fields from one or more arrays/maps.
  - JSON MERGE clause allows you to make a set of changes to a JSON document.
  - SET TTL clause updates the expiration time of the row.
- The `WHERE` clause specifies what row to update. The current implementation supports both single-row and multiple-row updates, so the `WHERE` clause must specify a complete primary key for single-row updates and for multiple-row updates you must specify a shard key, ensuring that all rows reside on the same shard. This enables Oracle NoSQL Database to perform this update in an ACID transaction.
- There is an optional `RETURNING` clause which acts the same way as the `SELECT` clause: it can be a "*", in which case, the full updated row will be returned, or it can have a list of expressions specifying what needs to be returned. The `RETURNING` clause works only when the `WHERE` clause includes the complete primary key.

  Note:

  Currently, it does not support multiple-row updates.
- Furthermore, if no row satisfies the `WHERE` conditions, the update statement returns an empty result.
- There is also a limit on the number of records a single update query can update. By default, an update query can modify up to 1,000 records. To override it, use `setLimit(int limit)` method, but avoid setting it too high, as it may cause timeouts or increased latency. See `QueryRequest.setLimit(int limit)`, for more details.

Example 1: Simple example to change the column values.

    UPDATE BaggageInfo
    SET contactPhone = "823-384-1964",
    confNo = "LE6J4Y"
    WHERE ticketNo = 1762344493810

Explanation: In the query above, you use the SET clause to update a few column values for a given ticket number.

Example 2: Update row data and fetch the values with a RETURNING clause.

    UPDATE BaggageInfo
    SET contactPhone = "823-384-1964",
    confNo = "LE6J4Y"
    WHERE ticketNo = 1762344493810 RETURNING *

Explanation: In the query above, you use the RETURNING clause to fetch back the data after the UPDATE transaction is completed.

Output:

    {"ticketNo":1762344493810,"fullName":"Adam Phillips","gender":"M","contactPhone":"823-384-1964",
    "confNo":"LE6J4Y",
    "bagInfo":{"bagInfo":[{"bagArrivalDate":"2019.02.02 at 03:13:00 AEDT","flightLegs":
    [{"actions":[{"actionAt":"MIA","actionCode":"ONLOAD to LAX","actionTime":"2019.02.01 at 01:13:00 EST"},
    {"actionAt":"MIA","actionCode":"BagTag Scan at MIA","actionTime":"2019.02.01 at 00:47:00 EST"},
    {"actionAt":"MIA","actionCode":"Checkin at MIA","actionTime":"2019.01.31 at 23:38:00 EST"}],
    "estimatedArrival":"2019.02.01 at 03:00:00 PST","flightDate":"2019.02.01 at 01:00:00 EST",
    "flightNo":"BM604","fltRouteDest":"LAX","fltRouteSrc":"MIA"},{"actions":
    [{"actionAt":"MEL","actionCode":"Offload to Carousel at MEL","actionTime":"2019.02.02 at 03:15:00 AEDT"},
    {"actionAt":"LAX","actionCode":"ONLOAD to MEL","actionTime":"2019.02.01 at 07:35:00 PST"},
    {"actionAt":"LAX","actionCode":"OFFLOAD from LAX","actionTime":"2019.02.01 at 07:18:00 PST"}],
    "estimatedArrival":"2019.02.02 at 03:15:00 AEDT","flightDate":"2019.01.31 at 22:13:00 PST",
    "flightNo":"BM667","fltRouteDest":"MEL","fltRouteSrc":"LAX"}],"id":"79039899165297",
    "lastActionCode":"OFFLOAD","lastActionDesc":"OFFLOAD","lastSeenStation":"MEL",
    "lastSeenTimeGmt":"2019.02.02 at 03:13:00 AEDT","routing":"MIA/LAX/MEL","tagNum":"17657806255240"}]}}

Example 3: Update the account expiry date for a customer in the `stream_acct` table.

    UPDATE stream_acct SET account_expiry="2023-12-28T00:00:00.0Z" WHERE acct_Id=3

Explanation: In the query above, you use the SET clause to update the `account_expiry` field for a given account in the streaming media application.

Updating multiple rows

Create a table named `EmployeeInfo` with an integer `empID` column as the primary key, a string `department` column as the shard key, a string `fullName` column, and a JSON type `info` column.

    CREATE TABLE EmployeeInfo (
        empID INTEGER,
        department STRING,
        fullName STRING,
        info JSON,
        PRIMARY KEY(SHARD(department), empID))

Next, add the following rows:

    INSERT INTO EmployeeInfo VALUES (101, "HR", "Liam Phillips", {"salary":100000,"address":{"city":"Toronto","country":"Canada"}})

    INSERT INTO EmployeeInfo VALUES (102, "HR", "Emma Johnson", {"salary":105000,"address":{"city":"Melbourne","country":"Australia"}})

    INSERT INTO EmployeeInfo VALUES (103, "IT", "Carlos Martinez", {"salary":110000,"address":{"city":"Barcelona","country":"Spain"}})

    INSERT INTO EmployeeInfo VALUES (104, "Finance", "Sophia Becker", {"salary":130000,"address":{"city":"Munich","country":"Germany"}})

Example 1: Use `UPDATE` statement to update multiple rows

The following statement updates the specified field in the rows associated with the mentioned shard key.

    UPDATE EmployeeInfo emp
        SET emp.info.address.city="Oslo",
        SET emp.info.address.country="Norway",
        SET emp.info.salary = emp.info.salary + 5000
     where department="HR"

Explanation: In the above query, the `SET` clause updates the `info.address.city` field to 'Oslo', the `info.address.country` field to 'Norway', and increases the `info.salary` field by 5000 for all rows where the department column, designated as the shard key, equals 'HR.' Because the `UPDATE` statement only mentions the shard key, the database returns only the number of rows it updates.

Output:

    +----------------+
     | NumRowsUpdated |
     +----------------+
     |              2 |
     +----------------+
    1 row returned

Now, run the `SELECT` query to verify the updated rows. Check that you have updated the `info.address.city` and `info.address.country` fields to 'Oslo' and 'Norway', respectively, and confirm that you have increased the `info.salary` field by 5000 for all employees working in the HR department.

    select * from EmployeeInfo

Output:

    +-------+------------+-----------------+----------------------------+
     | empID | department |    fullName     |            info            |
     +-------+------------+-----------------+----------------------------+
     |   103 | IT         | Carlos Martinez | address                    |
     |       |            |                 |     city    | Barcelona    |
     |       |            |                 |     country | Spain        |
     |       |            |                 | salary      | 110000       |
     +-------+------------+-----------------+----------------------------+
     |   101 | HR         | Liam Phillips   | address                    |
     |       |            |                 |     city    | Oslo         |
     |       |            |                 |     country | Norway       |
     |       |            |                 | salary      | 105000       |
     +-------+------------+-----------------+----------------------------+
     |   102 | HR         | Emma Johnson    | address                    |
     |       |            |                 |     city    | Oslo         |
     |       |            |                 |     country | Norway       |
     |       |            |                 | salary      | 110000       |
     +-------+------------+-----------------+----------------------------+
     |   104 | Finance    | Sophia Becker   | address                    |
     |       |            |                 |     city    | Munich       |
     |       |            |                 |     country | Germany      |
     |       |            |                 | salary      | 130000       |
     +-------+------------+-----------------+----------------------------+

    4 rows returned

Example 2: Use `UPDATE` statement with a `REURNING` clause

Example 2a: To update a single row with a `RETURNING` clause

    UPDATE EmployeeInfo emp SET emp.info.salary = emp.info.salary + 1000 WHERE empID=101 and department="HR" RETURNING *

Explanation: In the above query, the `SET` clause increments the salary by 1000 for an employee working in HR department with an employee ID of 101, followed by the `RETURNING` clause. Since the `WHERE` clause specifies the complete primary key, the update affects a single row, and the output is returned directly due to the presence of the `RETURNING` clause.

Output:

    +-------+------------+---------------+----------------------+
     | empID | department |   fullName    |         info         |
     +-------+------------+---------------+----------------------+
     |   101 | HR         | Liam Phillips | address              |
     |       |            |               |     city    | Oslo   |
     |       |            |               |     country | Norway |
     |       |            |               | salary      | 106000 |
     +-------+------------+---------------+----------------------+

    1 row returned

Example 2b: To update multiple rows with a `RETURNING` clause

    UPDATE EmployeeInfo emp SET emp.info.salary = emp.info.salary + 1000 WHERE department="HR" RETURNING *

Explanation: In the above query, the `SET` clause guides to increment the salary by 1000 for employees in HR department, followed by the `RETURNING` clause. As there are multiple employees in the HR department, the query throws an error because the `RETURNING` clause is currently not supported for multiple-row updates, and the `WHERE` clause must specify the complete primary key.

Output:

    Error handling command UPDATE EmployeeInfo emp SET emp.info.salary = emp.info.salary + 1000 WHERE department="HR" RETURNING *: Error: RETURNING clause is not supported unless the complete primary key is specified in the WHERE clause.

Updating JSON data

Example 1: Update a few fields, insert a nested JSON document, and remove an existing document from the `bagInfo` JSON array.

    UPDATE BaggageInfo b
    JSON MERGE b.bagInfo[0].flightLegs[size(b.bagInfo[0].flightLegs)-1] WITH PATCH {"flightNo" : "BM107", "actions" : NULL, "tempActions" : {
            "actionAt" : "LAX",
            "actionStatus" : "in transit"
          }},
    WHERE ticketNo = 1762344493810 RETURNING *

Explanation: The query above updates a passenger's baggage tracking information in an airline application. The `bagInfo` field is a JSON array that stores the passenger's baggage tracking information. You use the JSON MERGE patch to update the values in the last leg of the passenger's travel itinerary. To calculate the last element of the `flightLegs` JSON array, you use the built-in size function, which returns the number of travel segments and subtract it by one. In the patch expression, you supply the fields to be updated. Here, you modify the `flightNo` value, remove the actions field by supplying a NULL value, and insert a new JSON document `tempActions` in the `flightLegs` JSON array.

Output:

    {
      "ticketNo" : 1762344493810,
      "fullName" : "Adam Phillips",
      "gender" : "M",
      "contactPhone" : "893-324-1064",
      "confNo" : "LE6J4Z",
      "bagInfo" : [{
        "bagArrivalDate" : "2019-02-01T16:13:00Z",
        "flightLegs" : [{
          "actions" : [{ ... }],
          "estimatedArrival" : "2019-02-01T11:00:00Z",
          "flightDate" : "2019-02-01T06:00:00Z",
          "flightNo" : "BM604",
          "fltRouteDest" : "LAX",
          "fltRouteSrc" : "MIA"
        }, {
          "estimatedArrival" : "2019-02-01T16:15:00Z",
          "flightDate" : "2019-02-01T06:13:00Z",
          "flightNo" : "BM107",
          "fltRouteDest" : "MEL",
          "fltRouteSrc" : "LAX",
          "tempActions" : {
            "actionAt" : "LAX",
            "actionStatus" : "in transit"
          }
        }],
        "id" : "79039899165297",
        "lastActionCode" : "OFFLOAD",
        "lastActionDesc" : "OFFLOAD",
        "lastSeenStation" : "MEL",
        "lastSeenTimeGmt" : "2019-02-01T16:13:00Z",
        "routing" : "MIA/LAX/MEL",
        "tagNum" : "17657806255240"
      }]
    }

Note:

Here, the `bagInfo` field is an array of JSON documents, which represents the checked bags per passenger. If you don't specify the array element to be updated, the JSON MERGE clause replaces the entire `bagInfo` JSON array with the patch content. If there are more than one checked bag per passenger, you can use the JSON MERGE clause iteratively in the same UPDATE statement.

Example 2: Use JSON MERGE patch and PUT clause in an update statement.

You can use the JSON MERGE patch with all other update clauses (SET, ADD, PUT, REMOVE) in a single UPDATE statement as follows:

    UPDATE BaggageInfo b
    JSON MERGE b.bagInfo[0].flightLegs WITH PATCH {"flightNo" : "BM107", "actions" : NULL, "tempActions" : {
            "actionAt" : "LAX",
            "actionStatus" : "in transit"
          }},
    JSON MERGE b.bagInfo[1].flightLegs WITH PATCH {"flightNo" : "BM107", "actions" : NULL, "tempActions" : {
            "actionAt" : "LAX",
            "actionStatus" : "in transit"
          }},
    PUT b.bagInfo[0]{"bagArrivalDate" : "2019-03-13T00:00:00Z", "lastSeenStation" : "SFO", "routing" : "SFO/LAX",},
    PUT b.bagInfo[1]{"bagArrivalDate" : "2019-03-13T00:00:00Z", "lastSeenStation" : "SFO", "routing" : "SFO/LAX",}
    WHERE ticketNo = 1762320369957 RETURNING *

Explanation: In the query above, the passenger's record includes two checked bags. You use the JSON MERGE clause iteratively to update the baggage tracking information in both the elements of the `bagInfo` JSON array. Notice that the path expression in the target includes `flightLegs` object instead of an individual element of the `flightLegs` array. Therefore, the patch replaces the entire `flightLegs` JSON array with the `flightLegs` JSON object.

The PUT clause updates the `bagArrivalDate`, `lastSeenStation`, and `routing` field values in the specified elements of the `bagInfo` JSON array.

Output:

    {
      "ticketNo" : 1762320369957,
      "fullName" : "Lorenzo Phil",
      "gender" : "M",
      "contactPhone" : "364-610-4444",
      "confNo" : "QI3V6Z",
      "bagInfo" : [{
        "bagArrivalDate" : "2019-03-13T00:00:00Z",
        "flightLegs" : {
          "flightNo" : "BM107",
          "tempActions" : {
            "actionAt" : "LAX",
            "actionStatus" : "in transit"
          }
        },
        "id" : "79039899187755",
        "lastActionCode" : "OFFLOAD",
        "lastActionDesc" : "OFFLOAD",
        "lastSeenStation" : "SFO",
        "lastSeenTimeGmt" : "2019-03-12T15:05:00Z",
        "routing" : "SFO/LAX",
        "tagNum" : "17657806240001"
      }, {
        "bagArrivalDate" : "2019-03-13T00:00:00Z",
        "flightLegs" : {
          "flightNo" : "BM107",
          "tempActions" : {
            "actionAt" : "LAX",
            "actionStatus" : "in transit"
          }
        },
        "id" : "79039899197755",
        "lastActionCode" : "OFFLOAD",
        "lastActionDesc" : "OFFLOAD",
        "lastSeenStation" : "SFO",
        "lastSeenTimeGmt" : "2019-03-12T15:05:00Z",
        "routing" : "SFO/LAX",
        "tagNum" : "17657806340001"
      }]
    }

Updating rows in a JSON collection table

Consider a row from the JSON collection table created for a shopping application.

Example: Modify the erroneous shopper data record in the `storeAcct` table.

You can use the UPDATE statement to update fields in an existing document in the JSON collection tables. The UPDATE operation works in the same way as fixed schema tables.

    {"contactPhone":"1617114988","address":{"Dropbox":"Presidency College","city":"Kansas City","state":"Alabama","zip":95065},"cart":[{"item":"A4 sheets","priceperunit":500,"quantity":2},{"item":"Mobile Holder","priceperunit":700,"quantity":1}],"email":"lorphil@usmail.com","firstName":"Lorenzo","lastName":"Phil","notify":"yes","orders":[{"EstDelivery":"2023-11-15","item":"AG Novels 1","orderID":"101200,"priceperunit":950,"status":"Preparing to dispatch"},{"EstDelivery":"2023-11-01","item":"Wall paper","orderID":"101200,"priceperunit":950,"status":"Transit"}]}

Use the update clauses to correct a shopper's data as follows:

    UPDATE storeAcct s
    SET s.notify = "no",
    REMOVE s.cart [$element.item = "A4 sheets"],
    PUT s.address {"Block" : "C"},
    SET s.orders[0].EstDelivery =  "2023-11-17",
    ADD s.cart 1 {"item":"A3 sheets", "priceperunit":600, "quantity":2}
    WHERE s.contactPhone = "1617114988"

Explanation: In the above example, you update the shopper's record in the `storeAcct` table to correct a few inadvertent errors. This correction requires updates to various fields of the `storeAcct` table. The SET clause deactivates the notification setting in the shopper's data record. The REMOVE clause checks if any `item` field in the cart matches `A4 sheets` and deletes the corresponding element from the orders array. The PUT clause adds a new JSON field to indicate the landmark for delivery. The second SET clause accesses the deeply nested `EstDelivery` field and updates the estimated delivery date for the first item in the `orders` array. The ADD clause inserts a new element into the `cart` field to shortlist an additional item.

When you fetch the updated shopper's data, you get the following output:

Output:

    {"contactPhone":"1617114988","address":{"Block":"C","Dropbox":"Presidency College","city":"Kansas City","state":"Alabama","zip":95065},"cart":[{"item":"Mobile Holder","priceperunit":700,"quantity":1},{"item":"A3 sheets","priceperunit":600,"quantity":2}],"email":"lorphil@usmail.com","firstName":"Lorenzo","lastName":"Phil","notify":"no","orders":[{"EstDelivery":"2023-11-17","item":"AG Novels 1","orderID":"101200,"priceperunit":950,"status":"Preparing to dispatch"},{"EstDelivery":"2023-11-01","item":"Wall paper","orderID":"101200,"priceperunit":950,"status":"Transit"}]}