cql - Cassandra UPDATE primary key value

Question

I understand that this is not possible using an UPDATE.

What I would like to do instead, is migrate all rows with say PK=0 to new rows where PK=1. Are there any simple ways of achieving this?

Answer 1

For a relatively simple way, you could always do a quick COPY TO/FROM in cqlsh.

Let's say that I have a column family (table) called "emp" for employees.

CREATE TABLE stackoverflow.emp (
    id int PRIMARY KEY,
    fname text,
    lname text,
    role text
)

And for the purposes of this example, I have one row in it.

aploetz@cqlsh:stackoverflow> SELECT * FROM emp;

 id | fname | lname | role
----+-------+-------+-------------
  1 | Angel |   Pay | IT Engineer

If I want to re-create Angel with a new id, I can COPY the table's contents TO a .csv file:

aploetz@cqlsh:stackoverflow> COPY stackoverflow.emp TO '/home/aploetz/emp.csv';

1 rows exported in 0.036 seconds.

Now, I'll use my favorite editor to change the id of Angel to 2 in emp.csv. Note, that if you have multiple rows in your file (that don't need to be updated) this is your opportunity to remove them:

2,Angel,Pay,IT Engineer

I'll save the file, and then COPY the updated row back into Cassandra FROM the file:

aploetz@cqlsh:stackoverflow> COPY stackoverflow.emp FROM '/home/aploetz/emp.csv';

1 rows imported in 0.038 seconds.

Now Angel has two rows in the "emp" table.

aploetz@cqlsh:stackoverflow> SELECT * FROM emp;

 id | fname | lname | role
----+-------+-------+-------------
  1 | Angel |   Pay | IT Engineer
  2 | Angel |   Pay | IT Engineer

(2 rows)

For more information, check the DataStax doc on COPY.