DELETE: Removing Data

DELETE removes data. It can remove a whole row, or only some columns of a row.

Syntax

DELETE FROM table_name
WHERE primary_key = value;

DELETE column1, column2 FROM table_name
WHERE primary_key = value;

Example: delete a row

DELETE FROM employees WHERE id = 9;

SELECT * FROM employees WHERE id = 9;

Output:

 id | age | department | gender | name
----+-----+------------+--------+------


(0 rows)

Mike's row (id 9) is gone.

Example: delete only some columns

Put the column names between DELETE and FROM:

DELETE department FROM employees WHERE id = 2;

SELECT * FROM employees WHERE id = 2;

Output:

 id | age | department | gender | name
----+-----+------------+--------+------
  2 |  32 |       null |   male |  Bob

(1 rows)

Bob's row is still there. Only his department is now null.

Example: tables with a compound key

Remember the orders table, with PRIMARY KEY (customer, order_date). Here you can delete one row, or a whole partition at once:

-- one order of alice
DELETE FROM orders
WHERE customer = 'alice'
  AND order_date = '2024-02-10';

-- all of bob's orders (his whole partition)
DELETE FROM orders WHERE customer = 'bob';

SELECT * FROM orders;

Output:

 customer | order_date | amount
----------+------------+--------
    carol | 2024-03-11 |  89.50
    alice | 2024-01-15 | 120.50
    alice | 2024-03-02 |  75.00
    alice | 2024-04-18 | 250.00

(4 rows)

Alice lost one order, and bob has no orders left.

Like UPDATE, the WHERE of a DELETE must use primary key columns. You can't write "delete all orders where amount > 50".

Deletes are writes

Cassandra does not erase data right away. Think about a cluster where one node is down during your delete. When it comes back, it still has the old row. If the other nodes had simply erased the row, nothing would tell this node that the row was deleted, and the old row could come back.

So Cassandra writes a tombstone instead. A tombstone is a small marker that says "this data was deleted at this time". Later, a background cleanup called compaction removes the old data and the tombstone from disk.

This has two results for you:

  • A delete does not free disk space right away. The tombstone even uses a little more space for a while.
  • Many tombstones make reads slow, because Cassandra must read past them. So don't design a table where you insert and delete rows all the time, like a job queue.

For data that should disappear after some time, like login sessions, there is a better tool than DELETE. You will learn it in the next lesson.

In the exercise, David (id 4) has left the company. Delete his row.