Previous Next JavaScript must be enabled to correctly display this content

## Updating rows with an MR_COUNTER column

You can update an MR_COUNTER column in a multi-region table by incrementing or decrementing the values using standard arithmetic computations. For creating a table with MR_COUNTER column, see Create table using MR_COUNTER datatype

Note:

The system will return an error if you use the UPDATE clauses on MR_COUNTERS in the following scenarios:

- SET or PUT clauses to explicitly supply a value to an MR_COUNTER in the table.
- REMOVE clause to remove an MR_COUNTER column from the table.

You can use an ALTER statement to drop an MR_COUNTER column from the table. For more details, see Add or Remove an MR_COUNTER column.

Incrementing the value of an MR_COUNTER column.

A simple example would be incrementing the likes a user gets on a social media website.

Example 7-26 Increment an MR_COUNTER value

    CREATE Table myTable( name STRING,
                          count INTEGER AS MR_COUNTER,
                          PRIMARY KEY(name)) IN REGIONS DEN,LON

    INSERT INTO myTable(name) VALUES ("Chris')

    UPDATE myTable SET count = count + 10 WHERE name = "Chris"

Decrementing the value of an MR_COUNTER column.

The following example decrements the value of an MR_COUNTER.

Example 7-27 Decrement an MR_COUNTER value

    CREATE Table myTable( name STRING,
                          count INTEGER AS MR_COUNTER,
                          PRIMARY KEY(name)) IN REGIONS DEN,LON

    INSERT INTO myTable VALUES ("Chris',10)

    UPDATE myTable SET count = count - 4  WHERE name = "Chris"

Updating JSON MR_COUNTER values

You can update a JSON MR_COUNTER column (the same way as an MR_Counter column) in a multi-region table.

Example 7-28 Increment the value of a JSON MR_COUNTER column

You can update a JSON MR_COUNTER column by incrementing the value.

    UPDATE demoJSONMR a SET a.jsonWithCounter.counter = a.jsonWithCounter.counter + 1
    WHERE name = "Anna"

You can also update a JSON MR_COUNTER column by decrementing the value.

    UPDATE demoJSONMR a SET a.jsonWithCounter.counter = a.jsonWithCounter.counter - 1
    WHERE name = "Anna"

Updating counter values in both regions and perform a merge

When MR_COUNTER fields exist in both the remote JSON field and local JSON field, the system merges them as MR_COUNTER data types. You can update the MR_COUNTER fields in the remote and local region independently. The system automatically performs a merge on these concurrent modifications without user intervention.

Example 7-29 Update MR_COUNTER fields in the remote and local region

Consider the table `demoJSONMR` created in regions `FRA` and `LON` with the same definition as shown below.

    CREATE TABLE demoJSONMR(name STRING,
      jsonWithCounter JSON(counter as INTEGER MR_COUNTER,
                           person.count as LONG MR_COUNTER),
      PRIMARY KEY(name)) IN REGIONS FRA,LON

Step 1: Insert a row into the `demoJSONMR` table in the region `FRA`.

    INSERT INTO demoJSONMR VALUES (
          Anna,
            {
                "id" : 1,
                "counter" : NULL,
                "person" : {
                    "age" : 10,
                    "number" : 100
                }
            }
        }

Step 2: Update the row inserted above and increment JSON MR_COUNTER field "`counter`".

    UPDATE demoJSONMR a SET a.jsonWithCounter.counter = a.jsonWithCounter.counter + 1
    WHERE name = "Anna"

Step 3: In the Remote region `LON`, insert a row into the table with the same primary key "Anna", but different values for other fields.

    INSERT INTO exampleTable VALUES (
          Anna,
            {
                "id" : 2,
                "counter" : NULL,
                "person" : {
                    "age" : 10,
                    "number" : 101
                }
            }
        }

Step 4: In the Remote region `LON`, update the record and increment the JSON MR_COUNTER field "`counter`".

    UPDATE demoJSONMR a SET a.jsonWithCounter.counter = a.jsonWithCounter.counter + 1
    WHERE name = "Anna"

Step 5: In the statement above, the remote row gets updated. This update gets merged with the local row and the field "`counter`", as shown below.

    SELECT * FROM demoJSONMR WHERE name = "Anna"

Output:

    {"name":"Anna",
    "jsonWithCounter":{"counter":2,"id":2,"person":
                         {"age":10,"count":0,"number":101}}
    }

If the remote JSON and local JSON for MR_COUNTER have mismatched definitions, the INSERT or UPDATE operation is not performed on the mismatched schema. These rows are logged as incompatible rows.

Updating MR_COUNTER values in JSON collection table

Consider a JSON collection table created for a shopping application with MR_COUNTER. The `mycounter` field in the table is an MR_COUNTER with its value set to zero upon inserting data into the table.

Example 7-30 Update the MR_COUNTER value in a JSON collection table

The following is a sample row from the shopping application table:

    {"contactPhone":"1817113382","address":{"city":"Houston","number":401,"state":"TX","street":"Tex Ave","zip":95085},"cart":[{"item":"handbag","priceperunit":350,"quantity":1},{"item":"Lego","priceperunit":5500,"quantity":1}],"firstName":"Adam","lastName":"Smith","mycounter":0}

To update an MR_COUNTER value, you must supply the MR_COUNTER's name in the SET clause as illustrated in the query below:

    UPDATE storeAcctMR s SET s.mycounter = s.mycounter + 5 WHERE s.contactPhone="1817113382"

In this example, you increment the value of the MR_COUNTER by 5 for the shopper with the contact number`"1817113382"`. You get the following output when you fetch the shopper's record:

    {"contactPhone":"1817113382","address":{"city":"Houston","number":401,"state":"TX","street":"Tex Ave","zip":95085},"cart":[{"item":"handbag","priceperunit":350,"quantity":1},{"item":"Lego","priceperunit":5500,"quantity":1}],"firstName":"Adam","lastName":"Smith","mycounter":5}