Cassandra Query Language(CQL): Insert Into, Update, Delete (Example)

In this article, you will learn-

• Insert Data
• Upsert Data
• Update Data
• Delete Data
• Cassandra Where Clause

Insert Data

Command 'Insert into' writes data in Cassandra columns in row form. It will store only those columns that are given by the user. You have to necessarily specify just the primary key column.

It will not take any space for not given values. No results are returned after insertion.

Syntax

Insert into KeyspaceName.TableName(ColumnName1, ColumnName2, ColumnName3 . . . .)
values (Column1Value, Column2Value, Column3Value . . . .)

Example

Here is the snapshot of the executed command 'Insert into' that will insert one record in Cassandra table 'Student'.

Insert into University.Student(RollNo,Name,dept,Semester) values(2,'Michael','CS', 2);

After successful execution of the command 'Insert Into', one row will be inserted in the Cassandra table Student with RollNo 2, Name Michael, dept CS and Semester 2.

Here is the snapshot of the current database state.

Upsert Data

Cassandra does upsert. Upsert means that Cassandra will insert a row if a primary key does not exist already otherwise if primary key already exists, it will update that row.

Update Data

Command 'Update' is used to update the data in the Cassandra table. If no results are returned after updating data, it means data is successfully updated otherwise an error will be returned. Column values are changed in 'Set' clause while data is filtered with 'Where' clause.

Syntax

Update KeyspaceName.TableName 
Set ColumnName1=new Column1Value,
      ColumnName2=new Column2Value,
      ColumnName3=new Column3Value,
       .
       .
       .
Where ColumnName=ColumnValue

Example

Here is the screenshot that shows the database state before updating data.

Here is the snapshot of the executed command 'Update' that update the record in the Student table.

Update University.Student
Set name='Hayden'
Where rollno=1;

After successful execution of the command 'Update Student', student name will be changed from 'Clark' to 'Hayden' that has rollno 1.

Here is the screenshot that shows the database state after updating data.

Cassandra Delete Data

Command 'Delete' removes an entire row or some columns from the table Student. When data is deleted, it is not deleted from the table immediately. Instead deleted data is marked with a tombstone and are removed after compaction.

Syntax

Delete from KeyspaceName.TableName
	Where ColumnName1=ColumnValue

The above syntax will delete one or more rows depend upon data filtration in where clause.

Delete ColumnNames from KeyspaceName.TableName
	Where ColumnName1=ColumnValue

The above syntax will delete some columns from the table.

Example

Here is the snapshot that shows the current database state before deleting data.

Here is the snapshot of the command that will remove one row from the table Student.

Delete from University.Student where rollno=1;

After successful execution of the command 'Delete', one rows will be deleted from the table Student where rollno value is 1.

Here is the snapshot that shows the database state after deleting data.

What Cassandra does not support

There are following limitations in Cassandra query language (CQL).

1. CQL does not support aggregation queries like max, min, avg
2. CQL does not support group by, having queries.
3. CQL does not support joins.
4. CQL does not support OR queries.
5. CQL does not support wildcard queries.
6. CQL does not support Union, Intersection queries.
7. Table columns cannot be filtered without creating the index.
8. Greater than (>) and less than (<) query is only supported on clustering column.

   Cassandra query language is not suitable for analytics purposes because it has so many limitations.

Cassandra Where Clause

In Cassandra, data retrieval is a sensitive issue. The column is filtered in Cassandra by creating an index on non-primary key columns.

Syntax

Select ColumnNames from KeyspaceName.TableName Where ColumnName1=Column1Value AND
	ColumnName2=Column2Value AND
	.
	.
	.

Example

• Here is the snapshot that shows the data retrieval from Student table without data filtration.

select * from University.Student;

Two records are retrieved from Student table.

• Here is the snapshot that shows the data retrieval from Student with data filtration. One record is retrieved.

Data is filtered by name column. All the records are retrieved that has name equal to gtupapers.

select * from University.Student where name='gtupapers';