[SOLVED] Can you change the partition key of a particular row in a Cassandra table?

Can you change the partition key of a particular row in a Cassandra table?

I am unsure if you can change the partition key value of a particular row in a table. For example, if you had a table such that the PRIMARY KEY(name, title) and have some entry where name = "John", title = "New". Would we be able to run:

UPDATE table
SET name="Ron"
WHERE name="John" AND title="New";

I understand the general concept of the partition key such that it uniquely identifies rows in a partition while also identifying what nodes hold said partitions. So this leads to me to believe this would not run.

Solution

So this leads to me to believe this would not run.

You are correct. With Cassandra it is correct to say that UPDATE and INSERT typically do the same thing (under the hood). However in this case, that is not true. Running that UPDATE statement yields this message:

> UPDATE table SET name='Ron' WHERE name='John' AND title='New';
InvalidRequest: Error from server: code=2200 [Invalid query] message="PRIMARY KEY part name found in SET part"

This is because the UPDATE has additional checks around the WHERE clause. The best way to handle a situation like this, is to rewrite the row with the new key and then DELETE the old row.

And it's probably best to wrap those two statements in a BATCH, to ensure that entry doesn't end up in a weird state due to one of them failing.

BEGIN BATCH
    INSERT INTO table (name,title) VALUES ('Ron','New');
    DELETE FROM table WHERE name='John' AND title='New';
APPLY BATCH;