CASSANDRA CQL3 : Set value to entire column

Question

For my Cassandra Database, I need to set a value in column for all rows in my table.

I see in SQL, we can do :

UPDATE table SET column1= XXX;

but in CQL (in cqlsh), It doesn't work ! I don't want to update row by row until 9500 rows.

Do you have any suggestion ?

Thank you :)

Solution 1

As you are finding out, CQL != SQL. There is no way to do what you're asking in CQL, short of iterating through each row in your table.

Robert's suggestion about redefining column1 to be a static column may help. But static columns are tied to their partition key, so you would still need to specify that:

aploetz@cqlsh:stackoverflow2> UPDATE t SET s='XXX' WHERE k='k';

Also, it sounds like you only want to be able to set a column value for all rows. A static column won't work for you if you want that column value to be different for CQL rows within a partition (from the example in the DataStax docs):

aploetz@cqlsh:stackoverflow2> INSERT INTO t (k, s, i) VALUES ('k', 'I''m shared', 0);
aploetz@cqlsh:stackoverflow2> INSERT INTO t (k, s, i) VALUES ('k', 'I''m still shared', 1);
aploetz@cqlsh:stackoverflow2> SELECT * FROM t;

 k | i | s
---+---+------------------
 k | 0 | I'm still shared
 k | 1 | I'm still shared

(2 rows)

Note that the value of column s is the same across all CQL rows under partition key k. Just so you understand how that works.

Solution 2

You can use update query with IN clause instead of executing 9500 query. At first select primary_key from your table and then copy values to this query:

UPDATE table SET column1 = XXX WHERE primary_key IN (p1, p2, p3, ...);

Solution 3

I just added a new column to a table (+60000 rows), and I looked the way to initialize all the values of the column with something (not null), and I found nothing. Is not the same asked here, but if you drop and add the column my solution will solve it. So, this is what I did:

cqlsh> COPY tablename (primary_key, newcolumn) TO 'FILE.txt'

Open FILE.TXT on notepad++ and press Ctrl+H (Replace option), and replace all the \r\n with 'something\r\n'

And finally,

cqlsh> COPY tablename (primary_key, newcolumn) FROM 'FILE.txt'

Note1: You should be carefull if you primary_key contains \r\n.

Note2: May be in your SO the lines doesn't ends with \r\n.

Comments

[Reodont]

Actually, cqlsh allows exporting null values with custom string using WITH keyword. ` cqlsh> COPY tablename (primary_key, newcolumn) TO 'FILE.txt WITH NULL='<null>''` Where <null> is your custom null value.

[umop apisdn]

What if the primary key is composite (i.e. there are other clustering keys as well)?