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.
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.
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.
UPDATE demoJSONMR a SET a.jsonWithCounter.counter = a.jsonWithCounter.counter + 1
WHERE name = "Anna";
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.
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;
demoJSONMR
table in the
region
FRA
.INSERT INTO demoJSONMR VALUES (
Anna,
{
"id" : 1,
"counter" : NULL,
"person" : {
"age" : 10,
"number" : 100
}
}
}
counter
".UPDATE demoJSONMR a SET a.jsonWithCounter.counter = a.jsonWithCounter.counter + 1
WHERE name = "Anna"
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
}
}
}
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";
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.