While creating a hands-on exercice for Cassandra, I was facing a quite annoying limitation.
Let's take this table:
Upon a new participant joining the room, to be concurrency-proof (avoiding mutating the participants set if the room is deleted concurrently), I would like to issue this query:
Unfortunately the clause IF EXISTS is not allowed for UPDATE statements. Similarly I tried
It doesn't work either, it is not allowed to use one column of the primary key as condition column for LWT (why ? mystery).
So far, the only work-around I found is:
I added an extra column called name which is just the duplicate of the partition key room_name. It does work but is not very elegant.
I believe there are legit use cases for UPDATE ... IF EXISTS;