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. >>>>>> >>>>> >>>>> >>>> >> >
