[
https://issues.apache.org/jira/browse/PHOENIX-2271?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14903398#comment-14903398
]
Cameron Hatfield commented on PHOENIX-2271:
-------------------------------------------
Something along on the lines of (using TSQL syntax as an example, since it was
the first I ran across):
MERGE INTO A
USING (VALUES ('foo', 1), ('bar', 2)) AS B (ColA, ColB)
ON A.ColA = B.ColA
WHEN MATCHED THEN UPDATE SET A.ColB = B.ColB
WHEN NOT MATCHED THEN INSERT VALUES (B.ColA, B.ColB)
And then in theory you would have some type of extension to say "Use compare
and set".
Though it still seems a bit like trying to force a square peg into a round
hole. Yes, you can use merge to do it, but it seems like that would be more
confusing then having a dedicated syntax for it.
Another thing that might be worth considering is a syntax related to
postgresql's syntax they are considering, or at least their discussion of the
issues of MERGE vs their upsert statement:
https://wiki.postgresql.org/wiki/UPSERT#SQL_MERGE_syntax
Their syntax could also be extended to support a conditional clause in the case
of a conflict as well.
> Upsert - CheckAndPut like functionality
> ---------------------------------------
>
> Key: PHOENIX-2271
> URL: https://issues.apache.org/jira/browse/PHOENIX-2271
> Project: Phoenix
> Issue Type: Improvement
> Reporter: Babar Tareen
> Attachments: patch.diff
>
>
> The Upsert statement does not support HBase's checkAndPut api, thus making it
> difficult to conditionally update a row. Based on the comments from
> PHOENIX-6, I have implemented such functionality. The Upsert statement is
> modified to support compare clause, which allows us to pass in an expression.
> The expression is evaluated against the current record and Upsert is only
> performed when the expression evaluates to true. More details
> [here|https://github.com/babartareen/phoenix].
> h4. Examples
> Given that the FirstName is always set for the users, create a user record if
> one doesn't already exist.
> {code:sql}
> UPSERT INTO User (UserId, FirstName, LastName, Phone, Address, PIN) VALUES
> (1, 'Alice', 'A', '123 456 7890', 'Some St. in a city', 1122) COMPARE
> FirstName IS NULL;
> {code}
> Update the phone number for UserId '1' if the FirstName is set. Given that
> the FirstName is always set for the users, this will only update the record
> if it already exists.
> {code:sql}
> UPSERT INTO User (UserId, Phone) VALUES (1, '987 654 3210') COMPARE FirstName
> IS NOT NULL;
> {code}
> Update the phone number if the first name for UserId '1' starts with 'Al' and
> last name is 'A'
> {code:sql}
> UPSERT INTO User (UserId, Phone) VALUES (1, '987 654 3210') COMPARE FirstName
> LIKE 'Al%' AND LastName = 'A';
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
