Search code examples
cassandracqlcassandra-3.0cqlshcql3

Cassandra, Delete if a set contains value

I'm a beginner in Cassandra and I have a table like this:

CREATE TABLE Books(
Title text PRIMARY KEY,
Authors set<text>,
Family set <text>,
Publisher text,
Price decimal
);

(the other options are missing because it's only an example)

now I would like to execute this query:

DELETE Price FROM Books WHERE Authors CONTAINS 'J.K. Rowling' IF EXISTS;

But it doesn't work. I searched on Google but found nothing.

Hope somebody can help me and sorry if my english is not very good.

Solution

  • but it doesn't work.

    That doesn't really give us enough information to help you. Usually, you'll want to provide an error message. I built your table locally, inserted data, and tried your approach. This is the error that I see:

    InvalidRequest: Error from server: code=2200 [Invalid query]
   message="Some partition key parts are missing: title"

    DELETE requires that the appropriate PRIMARY KEY components be specified in the WHERE clause. In your case, Authors is not part of the PRIMARY KEY definition. Given the error message returned (and the table definition) specifying title is the only way to delete rows from this table.

    aploetz@cqlsh:stackoverflow> DELETE FROM Books 
    WHERE title = 'Harry Potter and the Chamber of Secrets'
    IF EXISTS;

 [applied]
-----------
      True

    Can I do a query like this? UPDATE Books SET Family = Family + {'Fantasy'} WHERE Authors CONTAINS 'J.K. Rowling';

    No. This fails for the same reason. Writes in Cassandra (INSERTs, UPDATEs, DELETEs are all writes) require the primary key (specifically, the partition key) in the WHERE clause. Without that, Cassandra can't figure out which node holds the data, and it needs that to perform the write.