Previous Next JavaScript must be enabled to correctly display this content

## Example: Updating JSON collection tables

You can update data in the JSON collection tables using the UPDATE statement. The UPDATE operation works in the same way as fixed schema tables.

Consider a row from a 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"}]}

Example 7-40 Correct a few inadvertent errors in the shopper's data

Use 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:

    {"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","priceperunit":950,"status":"Preparing to dispatch"},{"EstDelivery":"2023-11-01","item":"Wall paper","priceperunit":950,"status":"Transit"}]}