0

Given a table:

CREATE TABLE t (
    k text,
    v1 int,
    v2 int,
    PRIMARY KEY (k)
)

Is it possible to set v1 to some value and delete (set to null) v2 with a single query? Something like:

UPDATE t SET v1=100, v2=NULL WHERE k='somekey';

I have looked through the docs, but found nothing.

It would be a nice to have feature for two reasons:

  • Updating a table with a lots of columns using prepared statements is really painful now.
  • If my understanding is correct, row update by a single query should be atomic, whereas there are no guarantees for two consequent queries.
Wildfire
  • 6,358
  • 2
  • 34
  • 50

2 Answers2

3

The query you suggested works (at least) in CQL3:

cqlsh:ks> UPDATE t SET v1=100, v2=NULL WHERE k='somekey';
cqlsh:ks> select * from t;

 k       | v1  | v2
---------+-----+------
 somekey | 100 | null

If you want to do more complicated atomic updates e.g. across different row keys or column families, you can wrap separate UPDATEs in BEGIN BATCH...APPLY BATCH.

Richard
  • 11,050
  • 2
  • 46
  • 33
  • Oops, it was a silly question. I've got stuck with NULLs in PreparedStatements and didn't even try this query in cqlsh. I guess, I need to create another question. But thanks for your answer anyway. – Wildfire Jul 25 '13 at 10:15
0

Alternatively to updating to null, you might consider using the DELETE operation if you just want to remove data, which works atomically on columns and/or rows.

You mentioned you could not find the documentation. Here is the documentation related to Richard's excellent answer: - Updates on a single row by a single query are atomic by default. - Batch operations are also atomic since 1.2

John
  • 1,462
  • 10
  • 17