Inserts are always upserts

Suppose you try to insert data into a table with a primary key, which already exists; the data doesn't get updated in place. Rather, a new copy of the data is written, and the old copy is kept until it is deleted by some internal Cassandra mechanism. To demonstrate how this works, let's update one of the records using the INSERT statement.

Let's change Alice's email by running the following INSERT statement:

    INSERT INTO "users" 
    ("username", "email") 
    VALUES ('alice', 'alice@yahoo.com');

Let's query for the updated row and see what we get:

    SELECT * FROM "users" where "username"   = 'alice';

You can see that the e-mail column has been updated even if you use the INSERT statement with a primary key that already exists.

Next, we will delete the row and insert a non-existing row using the UPDATE command and look at the behavior.

First, let's delete the row and verify that it got deleted. To delete the row, execute the following statement:

    DELETE FROM "users" WHERE "username"   = 'alice';

To check whether it worked, run the preceding SELECT query:

    SELECT * FROM "users" where "username"   = 'alice';

You will get an empty result set or 0 rows. Next, let's try to use the UPDATE command to update non-existing rows with primary key (username) alice:

    UPDATE "users"
    SET "email" = 'alice@gmail.com',
    "encrypted_password" = 0x8914977ed729792e403da53024c6069a9158b8c4
    WHERE "username" = 'alice';

Now to verify, run the aforementioned SELECT query again, and you will see that the data got inserted successfully:

This tells us that the UPDATE statement has the same effect as an INSERT statement.