2

So I'm trying to do a CAS (compare-and-set) type operation in Cassandra, where I want to do a data update only if a particular non-primary-key column is NULL or < ? where ? is supplied by client-side code.

How do I do this? Something like the following doesn't work:

UPDATE my_dbs.foo SET col1=5 WHERE id=1 IF (col1 = NULL OR col1 < 4);

The error I get is similar to the following:

SyntaxException: <ErrorMessage code=2000 [Syntax error in CQL query] message="line 1:149 no viable alternative at input '(' (...

How do I do this in Cassandra 2.0/2.1?

Nishant Kelkar
  • 412
  • 1
  • 4
  • 20

2 Answers2

3

The OR logical operator is not supported in the IF clause. From the documentation here:

Synopsis:

UPDATE keyspace_name.table_name

USING option AND option

SET assignment, assignment, ...

WHERE row_specification

IF column_name = literal AND column_name = literal . . .

IF EXISTS

Perhaps either do two separate updates to cover both cases, or populate col1 with a default non-null value at insertion given this doesn't compromise your application logic.

Community
  • 1
  • 1
whoisdan
  • 86
  • 3
  • thanks so much, the latter sounds like a much better option for getting not-so-expensive reads- your former option depends a lot on consistency level of the read, imo. – Nishant Kelkar Apr 06 '16 at 15:49
  • @Nishant Kelkar you're very welcome. Yes I agree with you on the first option I mentioned. It would either be expensive reads or some sort of enforcement in the application layer. – whoisdan Apr 08 '16 at 02:13
1

Now you can do IF conditions with several "OR". Just use if field in (value1, value2)

UPDATE my_dbs.foo SET col1=5 WHERE id=1 IF col1 in (null, 1, 2, 3);

Hocas
  • 96
  • 6
  • I have found that (at least in driver v3.17.0) this works fine as a SimpleStatement, but it doesn't work with prepared statements. For TEXT columns at least, IF col1='' is treated differently to IF col1=null, but None and '' are both serialized to the same byte sequence when the tuple parameter is bound to the prepared statement. – Ian Goldby Dec 20 '21 at 13:35