yw, when correlated sub-query<https://issues.apache.org/jira/browse/PHOENIX-945>is implemented you could also get fancy with it and use dynamic statuses, something along the lines: UPSERT INTO queue(name, status) SELECT name, (select status from other_table where some_condition='true') FROM queue WHERE status = '4' and AND environment='QA'
Alex On Thu, May 22, 2014 at 10:04 PM, Dmitry Goldenberg <[email protected]>wrote: > Alex, > > This worked *perfectly*! Thank you so much. > > - Dmitry > > > On Thursday, May 22, 2014 9:57:32 PM UTC-4, AK wrote: > >> as I think about it, why can't you do >> UPSERT INTO queue(name, status) SELECT name, '1' FROM queue WHERE status >> = '4' and AND environment='QA' >> >> >> On Thu, May 22, 2014 at 9:48 PM, Dmitry Goldenberg >> <[email protected]>wrote: >> >>> Alex, >>> >>> Thanks for the reference, indeed that's what we'd want. I think it may >>> take me some time to come up to speed on the code enough to contribute a >>> patch, I'll see what I can do. In the meantime, if someone who's already >>> been in this code a lot could implement it perhaps that's the fastest way >>> to go. Is this slated for any upcoming release? >>> >>> - Dmitry >>> >>> >>> On Thu, May 22, 2014 at 9:37 PM, alex kamil <[email protected]> wrote: >>> >>>> Dmitry, >>>> >>>> looks like UPSERT SET<https://issues.apache.org/jira/browse/PHOENIX-792> >>>> might >>>> help,but it's not implemented yet, pls consider contributing a patch. also >>>> including the main mailing list >>>> >>>> Alex >>>> >>>> >>>> On Thu, May 22, 2014 at 8:39 PM, Dmitry Goldenberg <[email protected] >>>> > wrote: >>>> >>>>> Correction "A pure UPSERT doesn't handle a WHERE clause." >>>>> >>>>> >>>>> On Thursday, May 22, 2014 8:37:56 PM UTC-4, Dmitry Goldenberg wrote: >>>>> >>>>>> Hi Alex, >>>>>> >>>>>> Yes, I've thought of that. >>>>>> >>>>>> Examples: >>>>>> UPSERT INTO test.targetTable(col1, col2) SELECT col3, col4 FROM >>>>>> test.sourceTable WHERE col5 < 100 >>>>>> UPSERT INTO foo SELECT * FROM bar; >>>>>> >>>>>> This doesn't seem to have a way of providing a VALUES clause which is >>>>>> exactly what I need. I want to be able to select items in the queue where >>>>>> status=4, but reset the value to 1 and persist it. >>>>>> >>>>>> A pure UPSERT doesn't handle a VALUES clause either: >>>>>> UPSERT INTO TEST VALUES('foo','bar',3); >>>>>> UPSERT INTO TEST(NAME,ID) VALUES('foo',123); >>>>>> >>>>>> Ideally one would want a combo of these two. Perhaps a direct >>>>>> implementation of a SQL update? >>>>>> >>>>>> - Dmitry >>>>>> >>>>>> >>>>>> On Thu, May 22, 2014 at 8:19 PM, alex kamil <[email protected]>wrote: >>>>>> >>>>>>> Dmitry, how about upsert >>>>>>> select<http://phoenix.incubator.apache.org/language/index.html#upsert_select> >>>>>>> ? >>>>>>> >>>>>>> >>>>>>> thanks >>>>>>> Alex >>>>>>> >>>>>>> >>>>>>> On Thu, May 22, 2014 at 6:13 PM, Dmitry Goldenberg < >>>>>>> [email protected]> wrote: >>>>>>> >>>>>>>> Hi all, >>>>>>>> >>>>>>>> I'm trying to implement the type of logic with Phoenix which can be >>>>>>>> summarized as the below SQL statement: >>>>>>>> >>>>>>>> *UPDATE queue SET status = 1 WHERE status = 4 AND environment='QA'* >>>>>>>> >>>>>>>> I've noticed that UPSERT's don't support WHERE clauses. It seems to >>>>>>>> me then that I have to first create a view which would allow me to work >>>>>>>> with all the rows WHERE status=4 AND environment='QA'. >>>>>>>> >>>>>>>> So then, create view myview1 as select * from queue where status=4 >>>>>>>> and environment='QA'; I can tell that selection of rows works for >>>>>>>> this >>>>>>>> view. Next then, is to implement the UPDATE logic: >>>>>>>> >>>>>>>> *UPSERT INTO myview (status) VALUES (1);* >>>>>>>> >>>>>>>> which produces the error: >>>>>>>> *org.apache.phoenix.schema.ConstraintViolationException: >>>>>>>> MYVIEW.ENTRYID* may not be null. Of note, my 'queue' table has a >>>>>>>> primary key called ENTRYID which I populate via a sequence. >>>>>>>> >>>>>>>> Any ideas as to how I could get around this constraint violation? >>>>>>>> Or perhaps another way of implementing conditional updates (with a >>>>>>>> WHERE) >>>>>>>> of existing rows via Phoenix? >>>>>>>> >>>>>>>> I have looked at the IMMUTABLE_ROWS property as well. I wasn't >>>>>>>> clear on whether this is available on both tables and views and >>>>>>>> whether it >>>>>>>> defaults to mutable or immutable. I have tried setting this on the >>>>>>>> view, >>>>>>>> which wasn't allowed (*ERROR 1012 (42M03): Table undefined*). >>>>>>>> I've also tried setting IMMUTABLE_ROWS=false on the 'queue' table >>>>>>>> itself >>>>>>>> and that didn't make a difference. >>>>>>>> >>>>>>>> Any suggestions would be appreciated. >>>>>>>> >>>>>>>> -- >>>>>>>> You received this message because you are subscribed to the Google >>>>>>>> Groups "Phoenix HBase User" group. >>>>>>>> To unsubscribe from this group and stop receiving emails from it, >>>>>>>> send an email to [email protected]. >>>>>>>> For more options, visit https://groups.google.com/d/optout. >>>>>>>> >>>>>>> >>>>>>> >>>>>> >>>> >>> >> -- > You received this message because you are subscribed to the Google Groups > "Phoenix HBase User" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > For more options, visit https://groups.google.com/d/optout. >
