CQL Data Manipulation Commands

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 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.

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:

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

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

Another Syntax,

1. 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

Id	Name	Branch	City
001	Ayush	Electrical Engg	Boston
002	Aarav	Computer Engg	Stanford
003	Kabir	Applied physics	Pasadena

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.

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

Another Syntax,

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

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:

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

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

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

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. ‘DELETE’ keyword is used to perform this operation. Executing this command deletes the whole row as according to the identifier.

A Syntax of Delete Command:

1. DELETE <table name> USING <update parameter>
2. ... 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’.

1. cqlsh;keyspace1>DELETE student USING TTL 400
2. ... 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:
To create a batch the following syntax is used.

1. BEGIN BATCH
2. //different data manipulation command syntax
3. ;

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

1. APPLY BATCH;

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

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

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 comment box.