Difference between UPDATE and INSERT in Cassandra?

Question

What is the difference between UPDATE and INSERT when executing CQL against Cassandra?

It looks like there used to be no difference, but now the documentation says that INSERT does not support counters while UPDATE does.

Is there a "preferred" method to use? Or are there cases where one should be used over the other?

Thanks so much!

Answer 1

There is a subtle difference. Inserted records via INSERT remain if you set all non-key fields to null. Records inserted via UPDATE go away if you set all non-key fields to null.

Try this:

CREATE TABLE T (
  pk int,
  f1 int,
  PRIMARY KEY (pk)
);

INSERT INTO T (pk, f1) VALUES (1, 1);
UPDATE T SET f1=2 where pk=2;
SELECT * FROM T;

Returns:

 pk | f1
----+----
  1 |  1
  2 |  2

Now, update each row setting f1 to null.

UPDATE T SET f1 = null WHERE pk = 1;
UPDATE T SET f1 = null WHERE pk = 2;
SELECT * FROM T;

Note that row 1 remains, while row 2 is removed.

 pk | f1
----+------
  1 | null

If you look at these using Cassandra-cli, you will see a different in how the rows are added.

I'd sure like to know whether this is by design or a bug and see this behavior documented.