[
https://issues.apache.org/jira/browse/PHOENIX-957?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13984683#comment-13984683
]
James Taylor edited comment on PHOENIX-957 at 4/29/14 7:25 PM:
---------------------------------------------------------------
The UPDATE syntax would be nice, but you can already do this with the UPSERT
SELECT command:
{code}
CREATE TABLE table1 (pk1 INTEGER, pk2 INTEGER,
col1 INTEGER,
col2 INTEGER,
CONSTRAINT pk PRIMARY CONSTRAINT (pk1,pk2));
UPSERT INTO table1(pk1,pk2,col1,col2) VALUES(?,?,0, ?);
...
{code}
Then set auto commit to true on your connection (not mandatory, but your perf
will improve):
{code}
UPSERT INTO table1(pk1,pk2,col1)
SELECT pk1,pk2,col1+1 FROM table1 WHERE col2 > 7;
{code}
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:
{code}
CREATE SEQUENCE my_seq;
UPSERT INTO table1(pk1,pk2,col1)
SELECT pk1,pk2, NEXT VALUE FOR my_seq FROM table1 WHERE col2 > 7;
{code}
2) control the timestamp yourself to prevent the same timestamp from being used
to update the col1.
was (Author: jamestaylor):
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,
col2 INTEGER,
CONSTRAINT pk PRIMARY CONSTRAINT (pk1,pk2));
UPSERT INTO table1(pk1,pk2,col1,col2) 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 WHERE col2 > 7;
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 WHERE col2 > 7;
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)
