Updating rows with an MR_COUNTER column

You can update an MR_COUNTER column in a multi-region table.

Example 1: Incrementing the value of an MR_COUNTER column.

You can update an MR_COUNTER column by incrementing their values. A simple example would be incrementing the likes a user gets on a social media website.

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

Example 2: Decrementing the value of an MR_COUNTER column.

You can update an MR_COUNTER column by decrementing the 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";

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

Example: Incrementing 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";

Update 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 peforms a merge on these concurrent modifications without user intervention.

For example, consider the table demoJSONMR has been 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 one 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";
{"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.