Delete operation on a CQL3 table in Cassandra

Question

Say I have the following Column Family created using CQL3:

    CREATE TABLE users (
        id text,
        freq int,
        duration bigint,
        PRIMARY KEY (id, freq)
    )with clustering order by (freq desc);

I inserted some data in it e.g:

    Row(id=u'key', freq=3, duration=1)
    Row(id=u'key', freq=4, duration=2)
    Row(id=u'key', freq=5, duration=4)
    Row(id=u'key', freq=6, duration=6)

Now i want to do an update operation. Precisely, i want to update the 'freq' value for a particular row given above. Since CQL3 won't allow me update a primary key part directly; I'd have to delete that entry first and then insert it again. But problem is that it wouldn't allow me perform delete operation using a non partition key column 'duration'. e.g it wont allow me perform this operation :

    Delete from users where id ='key' and duration =1;

Does any one know a work around this ? i would really appreciate this.

Answer 1

Its time rethink on your meta model. Try to keep your meta model as flexible so as to support all such sort of operation. In other words your meta model's compound key combination should have the flexibility to handle such deletion or updation related queries.

Now coming to your example if you want update the freq column, then first of all you have to remove the row with that freq value, then re-insert a proper combination. Try to remove the row using your compound key combination, say in your case,

delete from users where id ='key' AND freq= 3

And then you can insert a fresh new row.