Dmitry,

looks like UPSERT SET 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 <dgoldenberg@kmwllc.com> 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 <alex.kamil@gmail.com> wrote:
Dmitry, how about upsert select?


thanks
Alex


On Thu, May 22, 2014 at 6:13 PM, Dmitry Goldenberg <dgoldenberg@kmwllc.com> 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 phoenix-hbase-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.