Batching conditional updates to a static column

An example of batching conditional updates combined with static columns.

As explained in the BATCH statement reference, you can batch conditional updates. This example shows batching conditional updates combined with using static columns. The example stores records about each purchase by user and includes the running balance of all a user's purchases.

CREATE TABLE purchases (
     user text,
     balance int static,
     expense_id int,
     amount int,
     description text,
     paid boolean,
     PRIMARY KEY (user, expense_id)
  );

Because the balance is static, all purchase records for a user have the same running balance.

The statements for inserting values into purchase records use the IF conditional clause.

BEGIN BATCH
  INSERT INTO purchases (user, balance) VALUES ('user1', -8) IF NOT EXISTS;
  INSERT INTO purchases (user, expense_id, amount, description, paid)
    VALUES ('user1', 1, 8, 'burrito', false);
APPLY BATCH;
  
BEGIN BATCH
  UPDATE purchases SET balance = -208 WHERE user='user1' IF balance = -8;
  INSERT INTO purchases (user, expense_id, amount, description, paid)
    VALUES ('user1', 2, 200, 'hotel room', false);
APPLY BATCH;

Because the column is static, you can provide only the partition key when updating the data. To update a non-static column, you would also have to provide a clustering key. Using batched conditional updates, you can maintain a running balance. If the balance were stored in a separate table, maintaining a running balance would not be posssible because a batch having conditional updates cannot span multiple partitions.

SELECT * FROM purchases;

At this point, the output is:

 user  | expense_id | balance | amount | description | paid
-------+------------+---------+--------+-------------+-------
 user1 |          1 |    -208 |      8 |     burrito | False
 user1 |          2 |    -208 |    200 |  hotel room | False

You could then use a conditional batch to update records to clear the balance.

BEGIN BATCH
  UPDATE purchases SET balance=-200 WHERE user='user1' IF balance=-208;
  UPDATE purchases SET paid=true WHERE user='user1' AND expense_id=1 IF paid=false;
APPLY BATCH;
  
SELECT * FROM purchases;

 user  | expense_id | balance | amount | description | paid
-------+------------+---------+--------+-------------+-------
 user1 |          1 |    -200 |      8 |     burrito | True
 user1 |          2 |    -200 |    200 |  hotel room | False