How to Perform Delete in CockroachDB

Introduction

This tutorial will explain how to perform delete record row in a table in CockroachDB. There are multiple methods for preforming table deletions in CockroachDB, including deleting a specific row, deleting specific multiple rows and deleting all rows in a table. At least a rudimentary working knowledge in SQL syntax is required when deleting a record in CockroachDB.

Prerequisites

  • A basic knowledge in SQL syntax.

  • CockroachDB must be properly installed and configured before beginning.

  • Confirm the CockroachDB service is running in the background by executing the following command:

cockroach start --insecure --listen-addr=localhost
  • Open another terminal and use the following command to start the CockroachDB SQL client.
cockroach sql --insecure --host=localhost:26257

How to Create the Sample Database

The following statements will create the raizel user and userdatabase database:

CREATE USER IF NOT EXISTS yeshua;

CREATE DATABASE userdatabase;

Then execute the following command to use the database:

SELECT DATABASE = userdatabase;

The user will be granted read and write permissions to the database for this tutorial using the following command:

GRANT ALL ON DATABASE userdatabase TO yeshua.

user \q to exit the SQL shell.

Now that a database has been created, a simple table can be created with the following statement:

CREATE TABLE tblusers(
id INT PRIMARY KEY,
name STRING,
age INT,
accessLevel STRING,
department STRING,
title STRING
);

Next, add some records in the tblUsers with the following commands:

INSERT INTO tblusers (id, name, age, accessLevel, department, title) VALUES
(1, 'mark', 22, 'user', 'accounting', 'assistant'),
(2, 'gene', 24, 'user', 'research', 'rankfile'),
(3, 'don', 25, 'admin', 'ict', 'supervisor');

Use the following command to verify the reconds were added sucessfully:

SELECT * FROM tblusers

The results should resemble the following:

id | name | age | accesslevel | department | title
+----+------+-----+-------------+------------+------------+
1 | mark | 22 | USER | accounting | assistant
2 | gene | 24 | USER | research | rankfile
3 | don | 25 | admin | ict | supervisor
(3 ROWS)

TIME: 6.55857ms

How to Deleting a Specific Row in CockroachDB

The below SQL statement will delete a specific row within a table “”tblUsers”” of CockroachDB database, using the “WHERE” clause against a unique item, such as id.

(This command will DELETE a ROW WITH an id OF 2)
DELETE FROM tblusers WHERE id = 2;

Execute the following command to confirm the above “DELETE” statement was successful:

SELECT * FROM tblusers;

The results should resemble the following:

id | name | age | accesslevel | department | title
+----+------+-----+-------------+------------+------------+
1 | mark | 22 | USER | accounting | assistant
3 | don | 25 | admin | ict | supervisor
(2 ROWS)

Notice that the row with an id of 2 was deleted from the table.

How to Delete all rows in CockroachDB

Use the below sql statement as an alternative way of deleting a process in a CockroachDB table. This command will delete all of the table’s rows:

DELETE FROM tblusers;

Execute the following sql statement to verify the “DELETE” command was successful:

SELECT * FROM tblusers;

The result should resemble the following:

id | name | age | accesslevel | department | title
+----+------+-----+-------------+------------+-------+
(0 ROWS)

How to Delete Rows Using Unique Column or the Primary Key in CockroachDB

Using a unique column in the DELETE command will restrict the delete process to only the matching criteria. Since there is no duplicate Primary Key, the process is unlikely to unintentionally delete other records.

This can be accomplished by using the following sql statement:

DELETE FROM tblusers WHERE id = 1 RETURNING *;

The RETURNING * clause instructs the SQL shell to return/display the deleted record for confirmation. The results should resemble the following:

id | name | age | accesslevel | department | title
+----+------+-----+-------------+------------+-----------+
1 | mark | 22 | USER | accounting | assistant
(1 ROW)

How to Delete Rows Using Non-Unique Column in CockroachDB

By deleting a row in this way, the row that returns “TRUE” for the “WHERE” clause will be removed. This can result in data being unintentionally deleted, as in the following example:

DELETE FROM tblusers WHERE age = 25 RETURNING *;

If the tblusers holds multiple user with the same age of 25, then all of those rows will be deleted.

Conclusion

This tutorial explained the basic ways of going about deleting a record in CockroachDB. Deletion methods included deleting a specific row, deleting multiple rows and deleting all rows in table. Bear in mind that by deleting a row using non-unique columns in CockroachDB can result in data being unintentionally deleted. Using a unique column in the “Delete” command will restrict the delete process to only the matching criteria.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.