Cassandra Update and Delete in Clustering column Using IN operator

Question

This is my table

CREATE TABLE quorum.omg (
id int,
a int,
b text,
c text,
PRIMARY KEY ((id, a), b)
) WITH CLUSTERING ORDER BY (b DESC)

When i'am doing a select statement using IN operator, it works fine for last partition key and last clustering key

SELECT * FROM  omg WHERE id=1 AND a IN ( 1,2) AND b IN ( 'a','b' ) ;

 id | a | b | c
----+---+---+----
  1 | 1 | b | hi
  1 | 2 | a | hi

But when i do update and delete it is throwing error like this

UPDATE omg SET c = 'lalala' WHERE id=1 AND a IN ( 1,2) AND b IN ( 'a','b' ) ;
InvalidRequest: code=2200 [Invalid query] message="Invalid operator IN for PRIMARY KEY part b"

DELETE from omg  WHERE id=1 AND a IN ( 1,2) AND b IN ( 'a','b' ) ;
InvalidRequest: code=2200 [Invalid query] message="Invalid operator IN for  PRIMARY KEY part b"

What is my mistake? Thanks in advance.

Answer 1

From the DataStax documentation on UPDATE (http://docs.datastax.com/en/cql/3.1/cql/cql_reference/update_r.html):

The IN relation is supported only for the last column of the partition key.

Your last partition key is a, yet you are trying to use it on your clustering key b. Try to UPDATE/DELETE with a specific, complete primary key in your WHERE clause.