LucidDbUpsert

From Eigenpedia

Syntax

MERGE INTO qualified-table-name [ [ AS ] alias ]
USING aliased-table-ref
ON predicate-expression
{ when-matched | when-not-matched | when-matched when-not-matched }

when-matched ::=
WHEN MATCHED THEN
UPDATE SET { unqualified-column-name = value-expression }, ...

when-not-matched ::=
WHEN NOT MATCHED THEN
INSERT [ ( unqualified-column-name, ... ) ]
VALUES row-constructor

See LucidDbValueExpression for specification of value-expression and predicate-expression.

See LucidDbSelectExpression for specification of aliased-table-ref.

See LucidDbExplicitValues for specification of row-constructor.

See LucidDbUniqueConstraints for interaction between upsert and uniqueness constraints.

Affected Row Counts

The "lastUpsertRowsInserted" session parameter (described in LucidDbSessionParameters) can be used to obtain the number of rows inserted by a MERGE. The number of rows updated can be computed by subtracting off the number of rows inserted from the number of rows affected (returned by the JDBC driver Statement.executeUpdate call).

Example

MERGE INTO warehouse.employee_dimension tgt
USING transform_schema.emp2_view src
ON src.empno=tgt.empno
WHEN MATCHED THEN
UPDATE SET ename=src.ename, dname=src.dname, job=src.job
WHEN NOT MATCHED THEN
INSERT(empno, ename, dname, job)
VALUES(src.empno, src.ename, src.dname, src.job);

SELECT CAST(param_value AS BIGINT) AS num_rows_inserted
FROM sys_root.user_session_parameters WHERE param_name='lastUpsertRowsInserted';