Updating rows with an MR

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

Example 1: Incrementing the value of an MR_COUNTER column.

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.

The following example decrements the value of an MR_COUNTER.

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"

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.

Update 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: 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"

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.

Update 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 0 upon inserting data into the 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}