1. Developers Guide
  2. Develop
  3. Inserting, Modifying, and Deleting Data
  4. Update Data
  5. Using SQL command to update data

Using SQL command to update data

An update statement can be used to update a row in a table.

  • The SET clause consists of two expressions: the target expression and the new-value expression. The target expression returns the items to be updated. The new-value expression may return zero or more items. If it returns an empty result, the SET is a no-op. If it returns more than one item, the items are enclosed inside a newly constructed array (this is the same as the way the SELECT clause treats multi-valued expressions in the select list)) So, effectively, the result of the new-value expression contains at most one item.
  • The WHERE clause specifies what row to update. In the current implementation, only single-row updates are allowed, so the WHERE clause must specify a complete primary key.
  • 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.
  • Furthermore, if no row satisfies the WHERE conditions, the update statement returns an empty result.

Example : Simple example to change the column values.

You are updating some column values for a given ticket number.
UPDATE BaggageInfo 
SET contactPhone = "823-384-1964", 
confNo = "LE6J4Y" 
WHERE ticketNo = 1762344493810

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

You could use the RETURNING clause to fetch back the data after the UPDATE clause is executed.
UPDATE BaggageInfo 
SET contactPhone = "823-384-1964", 
confNo = "LE6J4Y"
WHERE ticketNo = 1762344493810 RETURNING *
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: Update 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
1 row updated

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. Consider a row from the JSON collection table created for a shopping application. 

{"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"

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