5

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.

Aaron
  • 55,518
  • 11
  • 116
  • 132
Jagadeesh
  • 421
  • 5
  • 16
  • Refer this link http://mechanics.flite.com/blog/2014/01/08/the-in-operator-in-cassandra-cql/ – Mukesh Kalgude Jul 31 '15 at 07:31
  • 1
    possible duplicate of [Deleting in cassandra cql table using IN operator](http://stackoverflow.com/questions/27102579/deleting-in-cassandra-cql-table-using-in-operator) – Mateusz Dymczyk Jul 31 '15 at 07:34

2 Answers2

3

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.

Aaron
  • 55,518
  • 11
  • 116
  • 132
  • Thank you.... For my Conclusion: For UPDATE / DELETE The IN relation is supported only for the last column of the partition key. For SELECT The IN relation is supported for last partition key and last clustering key. – Jagadeesh Jul 31 '15 at 08:33
0

In general it’s best to avoid queries that require ALLOW FILTERING because they often require lots of data to be scanned even if only a small amount of data is returned, but I show that example because it is a supported use of the IN operator.

More detail to refer this link http://mechanics.flite.com/blog/2014/01/08/the-in-operator-in-cassandra-cql/

Mukesh Kalgude
  • 4,814
  • 2
  • 17
  • 32