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"}]}