how UPDATE rows in cassandra using only Partition Key?

Question

My table looks like this

create table Notes(
    user_id varchar,
    real_time timestamp,
    insertion_time timeuuid,
    read boolean PRIMARY KEY (user_id,real_time,insertion_time)
);

create index read_index on Notes (read);

I want update all the rows with user_id = 'xxx' without having to specify all the clustering indexes.

UPDATE Notes SET read = true where user_id = 'xxx'; // Says Error

Error: message="Missing mandatory PRIMARY KEY part real_time

I have tried creating a secondary index, but its not allowed on the primary key.

How can i solve this?

I chose user_id to be in the primary key cause i want to be able to do select * from Notes where user_id = 'xxx' should be possible.

Callisto

Answer 1

While this might be possible with a RDBMS and SQL, it is not possible with cql in Cassandra. From the DataStax documentation on the UPDATE command:

Each update statement requires a precise set of primary keys to be specified using a WHERE clause. You need to specify all keys in a table having compound and clustering columns.

You'll probably need to write something quick in Python (or one of the other drivers) to perform this type of update.

Unseasoned

Though the above answer is correct I think it's worth mentioning that you can use the IN clause for the last column of the primary key (i.e. insertion_time from the authors question). Depending on your use case this is useful to reduce the required amount of CQL statements. – Queston