CODEWITHSUNDEEP
if-statementcassandracql
user1491636
user1491636

Reputation: 2436

Cassandra CQL - update (insert) if not equal to

I have a scenario where I need to update (or insert) a record if a (non-key) field is not equal to some string OR the record does not exist. For example, given something like:

UPDATE mytable SET firstname='John', lastname='Doe' WHERE id='1' IF lastname != 'Doe';

If the lastname is not currently 'Doe', then update it, or if the record does not exist, update (insert) it. My assumption was that the IF condition would yield true if there was no record, but apparently not. Is there an alternative?

Upvotes: 5

Views: 6028

Answers (2)

Pedro Vidigal
Pedro Vidigal

Reputation: 422

In Cassandra an UPDATE behaves very similar as the INSERT statement, as explained in the Apache CQL Documenation:

"Note that unlike in SQL, UPDATE does not check the prior existence of the row by default (except through IF, see below): the row is created if none existed before, and updated otherwise. Furthermore, there are no means to know whether a creation or update occurred." - CQL Documentation - Update

I did a simple test and it did work:

cqlsh:test_keyspace> select * from conditional_updating ;

id | firstname | lastname
----+-----------+----------

(0 rows)

cqlsh:test_keyspace> update conditional_updating 
                       set firstname = 'John', 
                           lastname = 'Doe' 
                       WHERE id = 1 IF lastname != 'Doe';

 [applied]
-----------
  True

cqlsh:test_keyspace> select * from conditional_updating ;

 id | firstname | lastname
----+-----------+----------
  1 |      John |      Doe

(1 rows)

cqlsh:test_keyspace> update conditional_updating 
                       set lastname = 'New' 
                       WHERE id = 1 IF lastname != 'Doe';

 [applied] | lastname
-----------+----------
     False |      Doe

Note that using the IF condition isn't free Under the hood it triggers a lightweight transaction (LWT) (also known as CAS for Compara and SET). Such queries require a read and a write and they also need to reach consensus among all replicas, which makes it a bit onerous.

"But, please note that using IF conditions will incur a non-negligible performance cost (internally, Paxos will be used) so this should be used sparingly." - CQL Documentation - Update

If you are interested in knowing why Lightweight transactions are considered an anti-pattern in Cassandra I encourage you to have a look here: Lightweight Transactions In Cassandra

Upvotes: 4

Muhammad Zubair Saleem
Muhammad Zubair Saleem

Reputation: 517

Please refer to this documentation as this is what you need and in Cassandra UPDAtE Query act as an insert if not exists. update with condition

Example:

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

Upvotes: 0

Related Questions