CODEWITHSUNDEEP
cassandracql
Jagadeesh
Jagadeesh

Reputation: 431

Cassandra Update and Delete in Clustering column Using IN operator

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.

Upvotes: 5

Views: 4600

Answers (2)

Aaron
Aaron

Reputation: 57808

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.

Upvotes: 3

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

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/

Upvotes: 0

Related Questions