This fork of the Apache Phoenix project modifies the Upsert Values grammer to only update/insert the record if a given expression is evaluated to true. Only the row that is being modified can be referenced in the expressions. This allows to check the existing column values before performing the update. The functionality is similar to HBase's checkAndPut operation. To support this 'Compare' keyword is added to upsert values grammer. If the compare expression evaluates to true, the changes are applied, otherwise the changes are skipped and no errors are raised. Compare expressions support arithmetic and logical operators. contributor license agreements. See the NOTICE file distributed with this work for additional information regarding copyright ownership. The ASF licenses this file to You under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at

http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License. -->

CREATE TABLE User (
	UserId		BIGINT		PRIMARY KEY,
    FirstName	VARCHAR,
    LastName	VARCHAR,
    Phone		VARCHAR,
    Address		VARCHAR,
    PIN			INTEGER
);
    

Given that the FirstName is always set for the users, create a user record if one doesn't already exist.

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;

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.

UPSERT INTO User (UserId, Phone) VALUES (1, '987 654 3210') COMPARE FirstName IS NOT NULL;

Update the phone number if the first name for UserId '1' starts with 'Al' and last name is 'A'

UPSERT INTO User (UserId, Phone) VALUES (1, '987 654 3210') COMPARE FirstName LIKE 'Al%' AND LastName = 'A';

Update the address after verifying the pin

UPSERT INTO User (UserId, Address) VALUES (1, 'Other St. in the city') COMPARE PIN=1122;

The following upsert statements will result in the phone number to be '2222'. Upserts to a single row from a connection are sequentially applied to the row. This ensures that the upserts operate on the latest cell values for a record.

UPSERT INTO User (UserId, Phone) VALUES (1, '0000');
UPSERT INTO User (UserId, Phone) VALUES (1, '1111') COMPARE Phone = '0000';
UPSERT INTO User (UserId, Phone) VALUES (1, '2222') COMPARE Phone = '1111';
UPSERT INTO User (UserId, Phone) VALUES (1, '3333') COMPARE Phone = '1111';

Compare expressions are very similar to where clause expressions. Some of the limitations are

1. Left hand side of the expression should be a column identifier
2. Subqueries are not supported
3. Row key columns can not be used in compare expressions. This is becuase the compare expresions only operates on one row and that row is selected based on the row key value that was passed in the values segment.

Related to: PHOENIX-6

Open source contribution by RootMetrics