Previous Next JavaScript must be enabled to correctly display this content

## Example: Updating Rows

Let’s assume a table, called `People`, with only two columns: an integer `id` column and an `info` column of type JSON. Furthermore, let’s assume the following row to be updated:

    CREATE TABLE People (
        id INTEGER,
        info JSON,
    PRIMARY KEY(id))

    INSERT INTO People VALUES (
        0,
        {
            "firstName":"John",
            "lastName":"Doe",
            "profession":"software engineer",
            "income":200000,
            "address": {
                "city" : "San Fransisco",
                "state" : "CA",
                "phones" : [
                    { "areacode":415, "number":2840060, "kind":"office" },
                    { "areacode":650, "number":3789021, "kind":"mobile" },
                    { "areacode":415, "number":6096010, "kind":"home" }
                ]
            },
            "children": {
                "Anna" : {
                    "age" : 10,
                    "school" : "school_1",
                    "friends" : ["Anna", "John", "Maria"]
                },
                "Ron" : { "age" : 2 },
                "Mary" : {
                    "age" : 7,
                    "school" : "school_3",
                    "friends" : ["Anna", "Mark"]
                }
            }
        }
    )

Example 7-31 Use UPDATE statement to update various fields in the table

The following update statement updates various fields in the above row:

    UPDATE People p
        SET p.info.profession = "surfing instructor",
        SET p.info.address.city = "Santa Cruz",
        SET p.info.income = p.info.income / 10,
        SET p.info.children.values().age = $ + 1,
        ADD p.info.address.phones
          0 { "areacode":831, "number":5294368, "kind":"mobile" },
        REMOVE p.info.address.phones [$element.kind = "office"],
        PUT p.info.children.Ron { "friends" : ["Julie"] },
        ADD p.info.children.values().friends seq_concat("Ada", "Aris")
    WHERE id = 0
    RETURNING *

After the update, the row looks like this:

    {
        "id":0,
        "info":{
            "firstName":"John",
            "lastName":"Doe",
            "profession":"surfing instructor",
            "income":20000,
            "address":{
                "city":"Santa Cruz",
                "phones":[
                    {"areacode":831,"kind":"mobile","number":5294368},
                    {"areacode":650,"kind":"mobile","number":3789021},
                    {"areacode":415,"kind":"home","number":6096010}
                ],
                "state":"CA"
            },
            "children":{
                "Anna":{
                    "age":11,
                    "friends":["Anna","John","Maria","Ada","Aris"],
                    "school":"school_1"
                },
                "Ron":{
                    "age":3,
                    "friends":["Julie","Ada","Aris"]
                },
                "Mary":{
                    "age":8,
                    "friends":["Anna","Mark","Ada","Aris"],
                    "school":"school_3"
                }
            }
        }
    }

The first two SET clauses change the profession and city of John Doe. The third SET reduces his income to one-tenth. The fourth SET increases the age of his children by 1. Notice the use of the $ variable here: the expression `p.info.children.values().age` returns 3 ages; The SET will iterate over these ages, bind the $ variable to each age in turn, compute the expression $ + 1 for each age, and update the age with the new value. Notice that the income update can also have used a $ variable: `set p.info.income = $ / 10`. This would have saved the re-evaluation of the `p.info.income` path on the right-hand side or the "=".

The ADD clause adds a new phone at position 0 inside the phones array. The REMOVE removes all the office phones (only one in this example). The PUT clause adds a friend for Ron. In this clause, the expression`p.info.children.Ron` returns the value associated with the Ron child. This value is a map (the json object { "age" : 3 }) and becomes the target of the update. The 2nd expression in the PUT ({ "friends" : ["Julie"] }) constructs and returns a new map. The fields of this map are added to the target map. Finally, the last ADD clause adds the same two new friends to each child. See seq_concat function function.

Notice that the update query in this example would have been exactly the same if instead of type JSON, the info column had the following RECORD type:

    RECORD(
        firstName STRING,
        lastName STRING,
        profession STRING,
        income INTEGER,
        address RECORD(
            city STRING,
            state STRING,
            phones ARRAY(
                RECORD(
                    areacode INTEGER,
                    number INTEGER,
                    kind STRING
                )
            )
        ),
        children MAP(
            RECORD(
                age INTEGER,
                school STRING,
                friends ARRAY(STRING)
            )
        )
    )

Example: 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"}})

    INSERT INTO EmployeeInfo VALUES (105, "IT", "Ethan Dalmini", {"salary":250000,"address":{"city":"Cape Town","country":"South Africa"}})

    INSERT INTO EmployeeInfo VALUES (106, "HR", "James Peterson", {"salary":100500,"address":{"city":"Istanbul","country":"Turkey"}})

    INSERT INTO EmployeeInfo VALUES (107, "Finance", "John Doe", {"salary":250000,"address":{"city":"Seoul","country":"South Korea"}})

Example 7-32 Use `UPDATE` statement to update multiple rows in the table

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 |
     +----------------+
     |              3 |
     +----------------+

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       |
     +-------+------------+-----------------+----------------------------+
     |   105 | IT         | Ethan Dalmini   | address                    |
     |       |            |                 |     city    | Cape Town    |
     |       |            |                 |     country | South Africa |
     |       |            |                 | salary      | 250000       |
     +-------+------------+-----------------+----------------------------+
     |   101 | HR         | Liam Phillips   | address                    |
     |       |            |                 |     city    | Oslo         |
     |       |            |                 |     country | Norway       |
     |       |            |                 | salary      | 105000       |
     +-------+------------+-----------------+----------------------------+
     |   102 | HR         | Emma Johnson    | address                    |
     |       |            |                 |     city    | Oslo         |
     |       |            |                 |     country | Norway       |
     |       |            |                 | salary      | 110000       |
     +-------+------------+-----------------+----------------------------+
     |   106 | HR         | James Peterson  | address                    |
     |       |            |                 |     city    | Oslo         |
     |       |            |                 |     country | Norway       |
     |       |            |                 | salary      | 105500       |
     +-------+------------+-----------------+----------------------------+
     |   104 | Finance    | Sophia Becker   | address                    |
     |       |            |                 |     city    | Munich       |
     |       |            |                 |     country | Germany      |
     |       |            |                 | salary      | 130000       |
     +-------+------------+-----------------+----------------------------+
     |   107 | Finance    | John Doe        | address                    |
     |       |            |                 |     city    | Seoul        |
     |       |            |                 |     country | South Korea  |
     |       |            |                 | salary      | 250000       |
     +-------+------------+-----------------+----------------------------+

    7 rows returned