Conditionally Updating Columns

In this article, we will discuss how we can conditionally update columns and discuss how IF, IF EXISTS and IF NOT EXISTS conditional command is helpful for the conditional update.

The conditional update is very helpful because the query will return the results in either TRUE or FALSE and Transaction which follows IF, IF EXISTS and IF NOT EXISTS such type of command comes under for lightweight transaction in Cassandra. So, let’s have a look.

If the query matches a row, then IF EXISTS command will be successfully executed and return the results TRUE if the row matches otherwise FALSE. Let’s understand with an example.

CREATE TABLE Help_desk
(
 User_name  text,
 Request_id  uuid,
 Query_description text,
 Request_date date,
 Primary key(User_name)
); 

Now, insert some data into Help_desk.

INSERT INTO Help_desk (Request_id, User_name, Query_description, Request_date ) 
VALUES (uuid(), 'Ashish', ' App is not working', '2019-12-10');

INSERT INTO Help_desk (Request_id, User_name, Query_description, Request_date ) 
VALUES (uuid(), 'Rana', ' keyboard is not working', '2019-12-11');

INSERT INTO Help_desk (Request_id, User_name, Query_description, Request_date ) 
VALUES (uuid(), 'Abi', ' cpu is not working', '2019-12-13'); 

Let’s see the results.

select * 
from Help_desk; 

Output:

Now, here we are going to update the Help_desk table.
Let’s have a look.

UPDATE Help_desk
SET Query_description = 'Resolve and closed the ticket.' 
WHERE User_name = 'Ashish'  
IF EXISTS; 

In the above CQL query If the row exists then, it will return true.
Let’s see the results.

Output:

In the above CQL query If no row exists then, it will return true.
Let’s see the results.

 [applied]
-----------
   False 

UPDATE Help_desk
SET Request_id = uuid()
WHERE User_name = 'Ashish'
IF NOT EXISTS; 

Use the IF condition to apply tests to one or more other (non-primary key) column values in the matching row.

Now, here we are going to use the IF condition for other column values in the matching row. For example, to set a new UUID only if the Request_id matches.

UPDATE Help_desk
SET Request_id = uuid()  
WHERE User_name = 'Rana'
IF Request_id = NULL; 

Let’s see the results.

Output:

Here, we are going to check IF condition tests for a null value, for example:

UPDATE Help_desk 
SET Request_id = uuid()  
WHERE User_name = 'Rana'
IF Request_id = NULL; 

Let’s see the results.

Output:

Now, here we will see the final table output after all changes.

Let’s see the results.

select * 
from Help_desk; 

Output:

Note:

• In the above scenario, we are just mentioning a few points if A record matches and the Request_id column has no value is inserted.
• This is the second observation that if A record matches and the Request_id column has a value (is not null) then the statement fails.
• If there are No record matches for respective conditions, then a new record is created.

CAUTION: In CQL query Conditional updates are examples of lightweight transactions and They incur a non-negligible performance which costs and should be used sparingly.