Spanner - insert or update and increment previous value

Question

Spanner - insert or update and increment previous value.

CREATE TABLE dailyWeeklyPoints (
  userId STRING(MAX) NOT NULL,
  cumulativePoints INT64 NOT NULL,

) PRIMARY KEY(userId);

I have a schema like above:

1. I want to insert a new row if the row is not present and set cumulative value as the new value.
2. I want to update a row with incremented value if the row is present as current value = current value + new value.

I can't find a reference in spanner documentation for this case

Answer 1

You can implement this operation via a Read/Write transaction. In Java, this will look like the following:

DatabaseClient dbClient;
String userId;
Long newValue;

dbClient.RunTransaction(transaction -> {
  Struct row = transaction.readRow("dailyWeeklyPoints", 
    Key.of(userId), Arrays.asList("cumulativePoints"));
  if (row == null) {
    transaction.buffer(Mutation.newInsertBuilder("dailyWeeklyPoints")
      .set("userId")
      .to(userId)
      .set("cumulativePoints")
      .to(newValue));
  } else {
    transaction.buffer(Mutation.newUpdateBuilder("dailyWeeklyPoints")
      .set("userId")
      .to(userId)
      .set("cumulativePoints")
      .to(row.GetLong(0) + newValue));
  }
  return null;
});

You could also use a SELECT query followed by an INSERT statement or an UPDATE statement, using the same structure as above.