How do you update a Cassandra integer column using CQL?

Question

I have a table 'reviews' in Cassandra as mentioned below:

CREATE TABLE reviews (
  review_id text,
  downcount int,
  upcount int,
  creation_date timestamp,
  PRIMARY KEY (review_id)
)

Lets say I want to increase the upcount value by 1, how can I do that in Cassandra CQL?

When I do:

UPDATE reviews SET upcount = upcount +1 WHERE review_id = '123456';

It gives me the below error:

Bad Request: Invalid operation (upcount = upcount + 1) for non counter column upcount.

Answer 1

You can use Cassandra's counter columns data type. Keep in mind you cannot mix counter columns with non-counter columns in the same column family if the non-counter columns are not part of the composite PRIMARY KEY. So you have to separate the counters into another column family, say reviews_counts.

CREATE TABLE reviews (
  review_id text,
  creation_date timestamp,
  PRIMARY KEY (review_id)
)

CREATE TABLE reviews_counts (
  review_id text,
  downcount counter,
  upcount counter,
  PRIMARY KEY (review_id)
)

Now the increment statement should work.

UPDATE keyspace.reviews_counts 
SET upcount = upcount + 1
WHERE review_id = '123456'

Here is some more documentation about Cassandra counter columns. http://www.datastax.com/documentation/cql/3.0/cql/cql_using/use_counter_t.html