UPDATE: Changing Data

UPDATE changes the values of columns in a row that is already in the table.

Syntax

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE primary_key = value;

SET lists the new values. WHERE says which row to change.

Example: change one column

Grace (id 7) had a birthday. Change her age to 32:

UPDATE employees
SET age = 32
WHERE id = 7;

SELECT * FROM employees WHERE id = 7;

Output:

 id | age | department | gender | name
----+-----+------------+--------+-------
  7 |  32 |    Finance | female | Grace

(1 rows)

Only age changed. The other columns kept their values.

Example: change two columns

Separate the columns with commas:

UPDATE employees
SET department = 'IT', age = 33
WHERE id = 7;

SELECT * FROM employees WHERE id = 7;

Output:

 id | age | department | gender | name
----+-----+------------+--------+-------
  7 |  33 |         IT | female | Grace

(1 rows)

The WHERE needs the primary key

In SQL databases you can change many rows at once, for example "all employees in IT". Cassandra does not allow this. The WHERE must give the full primary key, so Cassandra knows the exact rows to change:

UPDATE employees
SET age = 40
WHERE department = 'IT';

Output:

InvalidRequest: Error from server: code=2200 [Invalid query] message="Some partition key parts are missing: id"

The reason is the same as for reads. Cassandra does not search the whole cluster for matching rows. If a table has a compound primary key, the WHERE must give every key column, the partition key and the clustering columns.

To change many rows, your app first reads their keys. Then it updates each row by its key.

You can't change a primary key column

The primary key decides where a row is stored, so you can't change it with SET:

UPDATE employees
SET id = 70
WHERE id = 7;

Output:

InvalidRequest: Error from server: code=2200 [Invalid query] message="PRIMARY KEY part id found in SET part"

To give a row a new key, insert a new row with the new key, then delete the old row. You will learn DELETE soon.

In the exercise, Emma is moving to the Sales department. Update her row.