CQL Data Manipulation Commands (Insert, Update, Delete, Batch)

1. Objective

In our last Cassandra tutorial, we had seen CQL Clauses. In this article, we are going to discuss Data Manipulation Commands in Cassandra Query Language (CQL).

These CQL Data Manipulation Commands helps to Insert, Update, Delete, and Batch Command with syntax and examples.

So, let’s start with CQL Data Manipulation Commands.

CQL Data Manipulation Commands (Insert, Update, Delete, Batch)

2. CQL Data Manipulation Commands

There will be many times when a user has to change the data in the tables. These commands are known as Data Manipulation Commands in CQL.

These commands are used to manipulate data in a table. The user can use CQL Data Manipulation Commands to change the contents of the table.
Let’s Study 10 Cassandra Shell Commands | CQL Shell Commands

a. INSERT Command

In a column family/table, there are many rows as per the requirement. These rows contain columns. A user can specify columns in advance. But sometimes the user may have to add a column after creating the table.

The user can use a data manipulation command to perform the operation. ‘INSERT’ keyword is used for this.

This command works in two ways. In first the values are added in a separate command. In the other, the values are added to the same line.
A Syntax of Insert Command:

INSERT INTO <table name>(<field name 1>,<field name 2>,<field name 3>.,...)
... VALUES ('value 1','value 2','value 3',....)
... USING <update parameter>;

The update parameter includes either a time stamp or time to live (TTL).
Another Syntax,

Do you know about Cassandra Data Manipulation Commands

INSERT INTO <table name> JSON '{"field name 1":"value 1","field name 2":"value 2","field name 3":"value 3",...}'

Example:
In the table ‘student’ given below,
Table.1- CQL Data Manipulation Commands – INSERT Command

Read about CQL Data Definition Command in detail
In this table, a row has to be added. That row includes 004, aaron, aerospace engg, new york city.

cqlsh;keyspace1>INSERT INTO student(id, name, branch, city)
... VALUES (004,'aaron','aerospace engg','new york city')
... USING TTL 86400;

Another Syntax,

cqlsh;keyspace1>INSERT INTO student JSON '{"id":"004","name":"aaron", "branch":"aerospace engg", "city":"new york city"}';

Have a look at Cassandra User Defined Types

b. UPDATE Command

These CQL data manipulation commands are used to update an existing data in a table. The user can use ‘UPDATE’ keyword. This execution replaces the previous value in a column of a row with a new value.
A Syntax of Update Command:

UPDATE <table name> USING <update parameter>
...SET <field name 1>=< value 1>,
< field name 2>=< value 2>,
< field name 3>=<value 3>,
.....
WHERE <field>=<value>;

Example: In the previous table, let us update the branch at id=002 to computer science and city to Berkley.

cqlsh;keyspace1>UPDATE student USING TTL 400
...SET branch=computer science,
... city=berkley,
WHERE id=002;

Let’s discuss Cassandra Data Types

c. DELETE Command

There may be some rows that the has to delete. These CQL data manipulation commands let the user delete data from a table. We use ‘DELETE’ keyword to perform this operation. Executing this command deletes the whole row as according to the identifier.
A Syntax of Delete Command:

DELETE <table name> USING <update parameter>
... WHERE <identifier>

Do you Know Cassandra vs MongoDB – 8 Major Factors of Difference
Example: In the table ‘student’, let us delete the last row ‘004’.

cqlsh;keyspace1>DELETE student USING TTL 400
... WHERE id=004;

d. BATCH Command

There may be few statements that have to updated constantly in an application. To ease this, a user can create a batch that will be repeated constantly. In other words, this command executes multiple data manipulation commands using a single statement. 
A Syntax of Batch Command:

You must learn about Cassandra API
To create a batch we use the following syntax.

BEGIN BATCH
//different data manipulation command syntax
;

To apply the batch, the user can use the following syntax.

APPLY BATCH;

Example: In the original table ‘student’, let us perform all the operations in the previous examples under a BATCH.

cqlsh;keyspace1> BEGIN BATCH
INSERT INTO student JSON '{"id":"004","name":"aaron", "branch":"aerospace engg", "city":"new york city"}';
UPDATE student USING TTL 400
...SET branch=computer science,
... city=berkley,
WHERE id=002;
DELETE student USING TTL 400
... WHERE id=004 ;
APPLY BATCH;

Test your Cassandra Knowledge and Competition

So, this was all about CQL Data Manipulations Commands. Hope you like the information.

3. Conclusion

Hence, in this CQL tutorial, we learned about the data manipulation commands in Cassandra Query Language. Furthermore, feel free to ask in the comment box.
Related Article – HBase vs Cassandra 
For reference