How To Perform an Update in CockroachDB

Introduction

If you’re using CockroachDB to manage your data, it’s important to know how to perform essential operations such as updating a record. Fortunately, updating a record in CockraochDB is a quick and easy task, especially if you have some familiarity with SQL. In this article, we’ll provide step-by-step instructions for updating records in CockroachDB.

Prerequisites

Before we can turn our attention to the SQL statements we’ll need to update records, it’s important to review the prerequisites for this task. There are a few key system requirements to consider:

  • You must ensure that CockroachDB has been properly installed and configured.

  • You also must ensure that the CockroachDB service is running in the background. To see if CockroachDB is running, use the following command:

cockroach start --insecure --listen-addr=localhost
  • Then after that open another terminal and use the following command to start the CockroachDB SQL client.
cockroach sql --insecure --host=localhost:26257
  • It’s helpful to have a working knowledge of SQL syntax.

Creating The Sample Database

Our first step will be to create a sample user and sample database to use in our examples. You can use the SQL statements shown below to create the yeshua sample user and the userdatabase sample database.

CREATE USER IF NOT EXISTS yeshua;

CREATE DATABASE userdatabase;

To use this database, use SELECT DATABASE = userdatabase;

For this tutorial, the user we just created will be given read and write permission for the database:

GRANT ALL ON DATABASE userdatabase TO yeshua.

Now that we created a database, a simple table can be created as well. To do this, we’ll use the following SQL statement:

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

Then, we’ll insert some records into the tblusers table.

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');

Let’s verify that our INSERT operation was successful before we move forward to the next step:

SELECT * FROM tblusers

The results you get back will look like 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

NOTE: To exit the SQL shell, just type \q.

Updating a Single Column in a Single Row In CockroachDB

At this point, we’re ready to try updating a record. The SQL statement shown below will update a single column “age” in a single row within the table “tblusers” in CockroachDB.

-- This will update the age value to 25 of the user
-- with an id of 2
UPDATE tblusers SET age = 25 WHERE id = 2;

We can verify that our UPDATE statement was successful by using the following statement:

SELECT id, name, age, department FROM tblusers WHERE id = 2;

The results will look like the output shown below:

id | name | age | department
+----+------+-----+------------+
2 | gene | 25 | research
(1 ROW)

Note that the value of "age" for user "gene" was updated from 24 to 25.

Updating Multiple Columns in a Single row In CockroachDB

Let’s look at another way of updating a record in CockroachDB.

In this example, we’ll promote our user “gene” to a supervisor; this means that the accessLevel for this user will be changed to admin. To accomplish this, we’ll use the following SQL statement:

UPDATE tblusers SET (accessLevel, title) = ('admin', 'assistant') WHERE id = 2;

In this case, the UPDATE operation will affect the values of two (2) columns (accessLevel and title).

Once again, we can verify that our UPDATE was successful with the following SQL statement.

SELECT * FROM tblusers WHERE id = 2;

You should get results that look something like this:

id | name | age | accesslevel | department | title
+----+------+-----+-------------+------------+-----------+
2 | gene | 25 | admin | research | assistant
(1 ROW)

Updating all rows in CockroachDB

NOTE: It’s important to proceed with caution when you execute SQL statements like the next example, as it will update every single row with matching criteria. This example will not be using the WHERE clause that specifies which row to update.

To do this, we’ll use the SQL statement shown below:

UPDATE tblusers SET age = 25;

If you try to run this in the SQL shell, this process will be rejected by default:

root@:26257/userdatabase> UPDATE tblusers SET age = 25;
pq: rejected: UPDATE WITHOUT WHERE clause (sql_safe_updates = TRUE)
root@:26257/userdatabase>

Notice that the sql_safe_updates option is set to true. To proceed with the UPDATE without the WHERE clause, disable sql_safe_updates by setting it’s value to false:

To do this, use the following command:

SET sql_safe_updates = FALSE;

Now try the update statement again: UPDATE tblusers SET age = 25;

SELECT * FROM tblusers;

You’ll see in the results that the value of "age" was set to 25 for all rows:

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

Conclusion

It’s important to know how to perform basic database operations when you work with CockroachDB– some common tasks include inserting, deleting and updating. In this article, we focused on learning how to do updates. Whether you need to update just a single record or all records in a table, the instructions in this article will ensure that you have no trouble updating records in CockroachDB.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed Redis,
MongoDB & Elasticsearch

Get Started

OR

Try CockroachDB
in Beta

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.