How to update clustering key in cassandra using update query?

Question

This is my table structure and I am updating the sequence with the following query:

Update styles set sequence=1 where styleid = 'CLASSIC';

I am getting error message as

PRIMARY KEY part sequence found in SET part
Missing PRIMARY KEY part sequence

CREATE TABLE styles (
  styleid ascii,
  sequence int,
  active boolean,
  image ascii,
  name ascii,
  PRIMARY KEY (styleid, sequence)
) WITH CLUSTERING ORDER BY (sequence DESC);

Please anyone help me to update the clustering key sequence so that it will be updated. Or any alternative method please share.

CREATE TABLE styles ( styleid ascii, sequence int, active boolean, image ascii, name ascii, PRIMARY KEY (styleid, sequence) ) WITH CLUSTERING ORDER BY (sequence DESC) AND bloom_filter_fp_chance=0.010000 AND caching='KEYS_ONLY' AND comment='' AND dclocal_read_repair_chance=0.100000 AND gc_grace_seconds=864000 AND read_repair_chance=0.000000 AND replicate_on_write='true' AND populate_io_cache_on_flush='false' AND compaction={'class': 'SizeTieredCompactionStrategy'} AND compression={'sstable_compression': 'LZ4Compressor'}; — Vicky Singh Gill, Nov 22 '14 at 09:58
I edited this question to remove the `sql` tag, and replace it with the `cql` tag. This is an important distinction to make, as sql != cql. cql is a *subset* of sql. Expecting cql to function like sql is an exercise that will ultimately lead you to frustration. — Aaron, Nov 22 '14 at 15:21

score 3 · Answer 1 · answered May 31 '16 at 17:07

Based on how Cassandra stores the data, updating clustering columns (Primary key) is not possible.

Rowkey which is a combination of **styleid** ->[sequence] -> [active boolean,image ascii, name ascii]

In order to point non-primary columns for updating/deleting, it needs to parse through styleid -> sequence partition to hit the columns.

Primary key once created can't be changed as it represents how data is stored in Cassandra. Hence updating 'sequence' here is not possible.

score 0 · Answer 2 · answered Oct 31 '16 at 14:51

While yes, updating the clustering columns directly is not possible in Cassandra, like everything, there are ways to do what you need to do.

You will need to create a temporary table that represents the data the way you want it to be. Then you will need to drop the old table, and then create a new table based on the temporary table.

I would suggest: 1) Backing up your initial table, because chances are the first time you try this you'll miss something. 2) Writing a program to make the changes you need (either a Javascript program, Java, whatever you are comfortable with). 3) Test, test, and then test again (first test locally, then move up to an integration style environment, and finally test on some sort of pre-prod environment, which is as close as you can get to your production environment.

How to update clustering key in cassandra using update query?

2 Answers2

Linked