You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@phoenix.apache.org by "James Taylor (JIRA)" <ji...@apache.org> on 2014/04/29 21:24:14 UTC

[jira] [Commented] (PHOENIX-957) increment column value

    [ https://issues.apache.org/jira/browse/PHOENIX-957?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13984683#comment-13984683 ] 

James Taylor commented on PHOENIX-957:
--------------------------------------

The UPDATE syntax would be nice, but you can already do this with the UPSERT SELECT command: 
CREATE TABLE table1 (pk1 INTEGER, pk2 INTEGER, col1 INTEGER, CONSTRAINT pk PRIMARY CONSTRAINT (pk1,pk2));
UPSERT INTO table1(pk1,pk2,col1) VALUES(?,?,0);
...

Then set auto commit to true on your connection (not mandatory, but your perf will improve):
UPSERT INTO table1(pk1,pk2,col1) SELECT pk1,pk2,col1+1 FROM table1

If you have the situation where there's lots of concurrency on updating this table, then you have two choices:
1) use sequences (http://phoenix.incubator.apache.org/sequences.html), as they're atomic:
CREATE SEQUENCE my_seq;
UPSERT INTO table1(pk1,pk2,col1) SELECT pk1,pk2, NEXT VALUE FOR my_seq FROM table1;

2) control the timestamp yourself to prevent the same timestamp from being used to update the col1.






> increment column value
> ----------------------
>
>                 Key: PHOENIX-957
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-957
>             Project: Phoenix
>          Issue Type: Improvement
>            Reporter: alex kamil
>
> allow incrementing column value using standard update syntax:
> UPDATE table1  SET col1 = col1 + 1 WHERE col2>7;
> for now using upsert select as a workaround:
> UPSERT INTO table1 (id, col1) SELECT id, col1+1 FROM table1 WHERE col2>7;



--
This message was sent by Atlassian JIRA
(v6.2#6252)