FWIW we have the exact same need. And we have been struggling with the differences in CQL between UPDATE and INSERT.
Our use case: We do in-memory dimensional aggregations that we want to write to C* using LWT. (so, it¹s a low-volume of writes, because we are doing aggregations across time windows) On ³commit², we: 1) Read current value for time window (which returns null if not exists for time window, or current_value if exists) 2) Then we need to UPSERT new_value for window where new_value = current_value + agg_value but only if no other node has updated the value For (2), we would love to see: UPSERT value=new_value where (not exists || value=read_value) (ignoring some intricacies) -brian --- Brian O'Neill Chief Technology Officer Health Market Science The Science of Better Results 2700 Horizon Drive King of Prussia, PA 19406 M: 215.588.6024 @boneill42 <http://www.twitter.com/boneill42> healthmarketscience.com This information transmitted in this email message is for the intended recipient only and may contain confidential and/or privileged material. If you received this email in error and are not the intended recipient, or the person responsible to deliver it to the intended recipient, please contact the sender at the email above and delete this email and any attachments and destroy any copies thereof. Any review, retransmission, dissemination, copying or other use of, or taking any action in reliance upon, this information by persons or entities other than the intended recipient is strictly prohibited. From: Robert Stupp <sn...@snazy.de> Reply-To: <user@cassandra.apache.org> Date: Tuesday, November 18, 2014 at 12:35 PM To: <user@cassandra.apache.org> Subject: Re: IF NOT EXISTS on UPDATE statements? >> > There is no way to mimic IF NOT EXISTS on UPDATE and it's not a bug. INSERT >> and UPDATE are not totally orthogonal > in CQL and you should use INSERT for actual insertion and UPDATE for updates > (granted, the database will not reject > our query if you break this rule but it's nonetheless the way it's intended to > be used). > > OK.. (and not trying to be difficult here). We can¹t have it both ways. One > of these use cases is a bug > > You¹re essentially saying ³don¹t do that, but yeah, you can do it.. ³ > > Either UPDATE should support IF NOT EXISTS or UPDATE should not perform > INSERTs. > UPDATE performs like INSERT in the meaning of an UPSERT - means: INSERT allows to write the same primary key again and UPDATE allows to write data to a non-existing primary key (effectively inserting data). (That¹s what NoSQL databases do.) Take that as an advantage / feature not present on other DBs. "UPDATE IF EXISTS³ and "INSERT IF NOT EXISTS³ are *expensive* operations (require serial-consistency/LWT which requires some more network roundtrips). "IF [NOT] EXISTS³ is basically some kind of "convenience³. And please take into account that UPDATE also has "IF column = value ³ condition (using LWT).
