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