Re: IF NOT EXISTS on UPDATE statements?
Hi there I was facing a similar requirement recently, e.g. UPDATE IF EXISTS and I found a work-around. CREATE TABLE my_table( partition_key int, duplicate_partition_key int, value text, PRIMARY KEY(partition_key)); At the beginning, I tried to query with : UPDATE my_table SET value = ... WHERE partition_key=xxx IF partition_key=xxx but I ran into Bad Request: PRIMARY KEY column 'id' cannot have IF conditions So the idea is to create a duplicate column of the partition key. This column is only set at the creation of the partition and never modified. With this I can simulate an IF EXISTS using UPDATE: UPDATE my_table SET value = ... WHERE partition_key=xxx IF duplicate_partition_key=xxx; On Tue, Nov 18, 2014 at 8:33 PM, Brian O'Neill b...@alumni.brown.edu wrote: Exactly. Perfect. Will do. Thanks Robert. -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 2:26 PM To: user@cassandra.apache.org Subject: Re: IF NOT EXISTS on UPDATE statements? For (2), we would love to see: UPSERT value=new_value where (not exists || value=read_value) That would be something like UPDATE … IF column=value OR NOT EXISTS“. Took at the C* source and that feels like a LHF (for 3.0) so I opened https://issues.apache.org/jira/browse/CASSANDRA-8335 for that. Fell free to comment on that :)
Re: IF NOT EXISTS on UPDATE statements?
On Mon, Nov 17, 2014 at 10:52 PM, Kevin Burton bur...@spinn3r.com wrote: There’s still a lot of weirdness in CQL. For example, you can do an INSERT with an UPDATE .. .which I’m generally fine with. Kind of make sense. However, with INSERT you can do IF NOT EXISTS. … but you can’t do the same thing on UPDATE. So I foolishly wrote all my code assuming that INSERT/UPDATE were orthogonal, but now they’re not. you can still do IF on UPDATE though… but it’s not possible to do IF mycolumn IS NULL .. so is there a way to mimic IF NOT EXISTS on UPDATE or is this just a bug? 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). -- Sylvain -- Founder/CEO Spinn3r.com Location: *San Francisco, CA* blog: http://burtonator.wordpress.com … or check out my Google+ profile https://plus.google.com/102718274791889610666/posts http://spinn3r.com
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. At least that’s the way I see it. Kevin -- Founder/CEO Spinn3r.com Location: *San Francisco, CA* blog: http://burtonator.wordpress.com … or check out my Google+ profile https://plus.google.com/102718274791889610666/posts http://spinn3r.com
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).
Re: IF NOT EXISTS on UPDATE statements?
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).
Re: IF NOT EXISTS on UPDATE statements?
For (2), we would love to see: UPSERT value=new_value where (not exists || value=read_value) That would be something like UPDATE … IF column=value OR NOT EXISTS“. Took at the C* source and that feels like a LHF (for 3.0) so I opened https://issues.apache.org/jira/browse/CASSANDRA-8335 for that. Fell free to comment on that :)
Re: IF NOT EXISTS on UPDATE statements?
Exactly. Perfect. Will do. Thanks Robert. -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 2:26 PM To: user@cassandra.apache.org Subject: Re: IF NOT EXISTS on UPDATE statements? For (2), we would love to see: UPSERT value=new_value where (not exists || value=read_value) That would be something like UPDATE IF column=value OR NOT EXISTS³. Took at the C* source and that feels like a LHF (for 3.0) so I opened https://issues.apache.org/jira/browse/CASSANDRA-8335 for that. Fell free to comment on that :)
IF NOT EXISTS on UPDATE statements?
There’s still a lot of weirdness in CQL. For example, you can do an INSERT with an UPDATE .. .which I’m generally fine with. Kind of make sense. However, with INSERT you can do IF NOT EXISTS. … but you can’t do the same thing on UPDATE. So I foolishly wrote all my code assuming that INSERT/UPDATE were orthogonal, but now they’re not. you can still do IF on UPDATE though… but it’s not possible to do IF mycolumn IS NULL .. so is there a way to mimic IF NOT EXISTS on UPDATE or is this just a bug? -- Founder/CEO Spinn3r.com Location: *San Francisco, CA* blog: http://burtonator.wordpress.com … or check out my Google+ profile https://plus.google.com/102718274791889610666/posts http://spinn3r.com
Re: IF NOT EXISTS on UPDATE statements?
So I foolishly wrote all my code assuming that INSERT/UPDATE were orthogonal, but now they’re not There are some subtle differences. INSERT will create marker columns, UPDATE won't touch/modify them. What are marker columns ? Some insights here: http://www.slideshare.net/doanduyhai/cassandra-introduction-40711134/87 you can still do IF on UPDATE though… but it’s not possible to do IF mycolumn IS NULL -- If mycolumn = null should work On Mon, Nov 17, 2014 at 10:52 PM, Kevin Burton bur...@spinn3r.com wrote: There’s still a lot of weirdness in CQL. For example, you can do an INSERT with an UPDATE .. .which I’m generally fine with. Kind of make sense. However, with INSERT you can do IF NOT EXISTS. … but you can’t do the same thing on UPDATE. So I foolishly wrote all my code assuming that INSERT/UPDATE were orthogonal, but now they’re not. you can still do IF on UPDATE though… but it’s not possible to do IF mycolumn IS NULL .. so is there a way to mimic IF NOT EXISTS on UPDATE or is this just a bug? -- Founder/CEO Spinn3r.com Location: *San Francisco, CA* blog: http://burtonator.wordpress.com … or check out my Google+ profile https://plus.google.com/102718274791889610666/posts http://spinn3r.com
Re: IF NOT EXISTS on UPDATE statements?
you can still do IF on UPDATE though… but it’s not possible to do IF mycolumn IS NULL -- If mycolumn = null should work Alas.. it doesn’t :-/ -- Founder/CEO Spinn3r.com Location: *San Francisco, CA* blog: http://burtonator.wordpress.com … or check out my Google+ profile https://plus.google.com/102718274791889610666/posts http://spinn3r.com
Re: IF NOT EXISTS on UPDATE statements?
Just tested with C* 2.1.1 cqlsh:test CREATE TABLE simple(id int PRIMARY KEY, val text); cqlsh:test INSERT INTO simple (id) VALUES (1); cqlsh:test SELECT * FROM simple ; id | val +-- 1 | null (1 rows) cqlsh:test UPDATE simple SET val = 'new val' WHERE id=1 *IF val = null*; [applied] --- True cqlsh:test SELECT * FROM simple ; id | val +- 1 | new val (1 rows) On Tue, Nov 18, 2014 at 12:12 AM, Kevin Burton bur...@spinn3r.com wrote: you can still do IF on UPDATE though… but it’s not possible to do IF mycolumn IS NULL -- If mycolumn = null should work Alas.. it doesn’t :-/ -- Founder/CEO Spinn3r.com Location: *San Francisco, CA* blog: http://burtonator.wordpress.com … or check out my Google+ profile https://plus.google.com/102718274791889610666/posts http://spinn3r.com
Re: IF NOT EXISTS on UPDATE statements?
Oh yes. That will work because a value is already there. I’m talking if the value does not exist. Otherwise I’d have to insert a null first. On Mon, Nov 17, 2014 at 3:30 PM, DuyHai Doan doanduy...@gmail.com wrote: Just tested with C* 2.1.1 cqlsh:test CREATE TABLE simple(id int PRIMARY KEY, val text); cqlsh:test INSERT INTO simple (id) VALUES (1); cqlsh:test SELECT * FROM simple ; id | val +-- 1 | null (1 rows) cqlsh:test UPDATE simple SET val = 'new val' WHERE id=1 *IF val = null*; [applied] --- True cqlsh:test SELECT * FROM simple ; id | val +- 1 | new val (1 rows) On Tue, Nov 18, 2014 at 12:12 AM, Kevin Burton bur...@spinn3r.com wrote: you can still do IF on UPDATE though… but it’s not possible to do IF mycolumn IS NULL -- If mycolumn = null should work Alas.. it doesn’t :-/ -- Founder/CEO Spinn3r.com Location: *San Francisco, CA* blog: http://burtonator.wordpress.com … or check out my Google+ profile https://plus.google.com/102718274791889610666/posts http://spinn3r.com -- Founder/CEO Spinn3r.com Location: *San Francisco, CA* blog: http://burtonator.wordpress.com … or check out my Google+ profile https://plus.google.com/102718274791889610666/posts http://spinn3r.com
