11 KiB
Executable File
MERGE
supported in urQL parser, not yet supported in Obelisk some experimental stuff proposed here, take with a grain of salt
MERGE
is a statement that conditionally performs INSERT
, UPDATE
, or DELETE
operations. It modifies the content of the <target-table>
, merging data from the <source-table>
and static <common-table-expression>
sources.
First, the MERGE command performs an outer join from <target-table>
to <source-table>
using ON <merge-predicate>
producing candidate change rows.
For each candidate change row, the MATCHED
or NOT MATCHED
status is determined. If applicable, NOT MATCHED
on <target-table>
or <source-table>
is set.
Finally, for each candidate change row, the first WHEN
clause under the applicable MATCHED
/NOT MATCHED
condition is executed.
A WHEN
clause without AND <predicate>
implies unconditional execution. Subsequent WHEN
clauses for the same target/source matching condition are not allowed.
If no WHEN
clause evaluates as true, the target row remains unchanged, which is equivalent to specifying NOP
.
MERGE
actions have the same effect as the standard UPDATE
, INSERT
, or DELETE
commands.
MERGE
can update the contents of an existing target <table>
, produce a new <table>
, or produce a new virtual <table-set>
.
When MERGE INTO
is specified or implied, <target-table>
must be a base <table>
and contents are updated in place. PRODUCING NEW
may not be specified.
When MERGE FROM
is specified, PRODUCING NEW
must also be specified. <target-table>
can be a base <table>
or any virtual table (i.e. <view>
or PASS-THRU
<table-set>
).
If <new-table>
is specified, it will be created as a new <table>
and populated in the same way as when <target-table>
is updated with MERGE INTO
.
The output <table-set>
's row type will correspond to the row type of <target-table>
. And its primary index (in the case when <new-table>
is produced) will correspond to the primary index of <target-table>
. The <target-table>
's <foreign-key>
s are not replicated.
If the resulting virtual-table row type is a union type, then the output must be a virtual-table PASS-THRU
, not an update to <target-table>
or creation of <new-table>
as base <table>
.
<merge> ::=
MERGE [ { INTO | FROM } ] <target-table> [ [ AS ] <alias> ]
[ PRODUCING NEW <new-table> ]
USING <source-table> [ [ AS ] <alias> ]
[ [ SCALAR ] [ ,...n ] ]
[ ON <merge-predicate> ]
[ WHEN MATCHED [ AND <matched-predicate> ]
THEN <merge-matched> ] [ ...n ]
[ WHEN NOT MATCHED [ BY TARGET ] [ AND <unmatched-target-predicate> ]
THEN <merge-not-matched> ] [ ...n ]
[ WHEN NOT MATCHED BY SOURCE [ AND <unmatched-source-predicate> ]
THEN <merge-matched> ] [ ...n ]
[ <as-of-time> ]
<target-table> ::= <table-set>
<new-table> ::= <table-set>
<source-table> ::= <table-set>
<matched-predicate> ::= <predicate>
<unmatched-target-predicate> ::= <predicate>
<unmatched-source-predicate> ::= <predicate>
<merge-matched> ::=
{ UPDATE [ SET ] { <column> = <scalar-expression> } [ ,...n ]
| DELETE
| NOP
}
Specifies the update or delete action that is applied to all rows of <target-table>
that don't match the rows returned by <table-source>
ON <merge-predicate>
, and which satisfy any additional predicate.
<column>
Identifies column in <target-table>
. Each column may be referenced once.
<scalar-expression>
Aura must match corresponding aura in <target-table>
.
DELETE
Delete the matched target row.
NOP
No operation performed.
<merge-not-matched> ::=
INSERT [ ( <column> [ ,...n ] ) ]
VALUES ( <scalar-expression> [ ,...n ] )
| NOP
<column>
Identifies column in <target-table>
. Each column may be referenced once.
<scalar-expression>
Aura must match corresponding aura in <target-table>
.
The count out INSERT
columns and VALUES
must match.
NOP
No operation performed.
API
+$ merge
$:
%merge
target-table=table-set
new-table=(unit table-set)
source-table=table-set
predicate=predicate
matched=(list matching)
unmatched-by-target=(list matching)
unmatched-by-source=(list matching)
as-of=(unit as-of)
==
Arguments
[ { INTO | FROM } ] <target-table> [ [ AS ] <alias> ]
<alias>
is alternative name to reference <target-table>
in WHEN
clauses and predicates.
If { INTO | FROM }
is not specified, INTO
is the default.
If INTO
is specified (or implied) then <target-table>
is a base-table
If <target-table>
is a virtual-table -- any <table-set>
other than a base-table, i.e. qualified <view>
, <common-table-expression>
, *
, or ( column-1 [,...column-n] )
-- then FROM
is required.
INTO
must not accompany PRODUCING NEW
argument.
FROM
must accompany PRODUCING NEW
argument.
<target-table>
is the table, view, or CTE against which the data rows from <table-source>
are matched based on <merge-predicate>
.
If FROM
is specified, any INSERT
, UPDATE
, or DELETE
operations specified by the WHEN
clauses, as well as matched but otherwise unaffected target table rows, produce a new <table-set>
as specified by the PRODUCING NEW
clause.
[ PRODUCING NEW <new-table>
]
Required when FROM
is specified.
Prohibited when INTO
is specified or implied.
If <new-table>
has the syntax of a qualified <table>
, it cannot already exist.
If <target-table>
has a row type which is a union type, <new-table>
cannot be a base <table>
.
USING <source-table> [ [ AS ] <alias> ]
Specifies the data source that is matched with the data rows in <target-table>
joining on <merge-predicate>
. <table-source>
can be any <table-set>
.
<alias>
is an alternative name to reference <source-table>
in WHEN
clauses and predicates.
[ [ SCALAR ] [ ,...n ] ]
TBD
ON <merge-predicate>
Specifies the conditions on which <table-source>
joins with <target-table>
, determining the matching and can be any valid <predicate>
not resulting in cartesian join.
If <merge-predicate>
is not specified, source and target must share row type and matching implies rows equal by value.
If <merge-predicate>
does not resolve for any row sub-type between the target and source it potentially creates NOT MATCHED
conditions BY TARGET
and BY SOURCE
on rows that otherwise are equal by value.
[ WHEN MATCHED [ AND <target-predicate> ] THEN <merge-matched> ] [ ...n ]
Specifies that all rows of <target-table>
, which join the rows returned by <table-source>
ON <merge-predicate>
or the implied join when ON
predicate not present, and satisfy <target-predicate>
(when present), result in some action according to the <merge-matched>
clause.
WHEN MATCHED
clause without AND <matched-predicate>
implies unconditionally apply the <merge-matched>
action.
If two or more WHEN MATCHED
clauses are specified only the last clause may be unaccompanied by AND <matched-predicate>
.
The first <matched-predicate>
evaluating to true determines the <merge-matched>
action.
If there is no unconditional <merge-matched>
action, it is the same as specifying NOP
for unconditional action.
[ WHEN NOT MATCHED [ BY TARGET ] [ AND <unmatched-target-predicate> ] THEN <merge-not-matched> ] [ ...n ]
Specifies the action on <target-table>
for every row returned by <table-source>
ON <merge-predicate>
that doesn't match a row in target-table, but satisfies <unmatched-target-predicate>
, if present. The action to take is specified by the <merge-not-matched>
clause.
WHEN NOT MATCHED BY TARGET
clause without AND <unmatched-target-predicate>
implies unconditionally apply the <merge-not-matched>
action.
If two or more WHEN NOT MATCHED BY TARGET
clauses are specified only the last clause may be unaccompanied by AND <unmatched-target-predicate>
.
The first <unmatched-target-predicate>
evaluating to true determines the <merge-not-matched>
action.
If there is no unconditional <merge-not-matched>
action, it is the same as specifying NOP
for unconditional action.
WHEN NOT MATCHED BY SOURCE [ AND <unmatched-source-predicate> ] THEN <merge-matched>
Specifies that all rows of <target-table>
, which don't match the rows returned by <table-source>
ON <merge-predicate>
, and that satisfy any additional search condition, are updated or deleted according to the <merge-matched>
clause.
WHEN NOT MATCHED BY SOURCE
clause without AND <unmatched-source-predicate>
implies unconditionally apply the <merge-matched>
action.
If two or more WHEN NOT MATCHED BY SOURCE
clauses are specified only the last clause may be unaccompanied by AND <unmatched-source-predicate>
.
The first <unmatched-source-predicate>
evaluating to true determines the <merge-matched>
action.
If there is no unconditional <merge-matched>
action, it is the same as specifying NOP
for unconditional action.
When no rows are returned by <table-source>
, columns in the source table can't be accessed, and therefore the <merge-matched>
action cannot reference columns in <table-source>
.
Remarks
When <target-table>
is updated in place or <new-table>
specified as a base <table>
, the command potentially results in a state change of the Obelisk agent.
Cross ship merges are not allowed.
In the case of multiple WHEN MATCHED
or WHEN NOT MATCHED
and overlapping predicates, the cases are processed in order, so the first successful case takes precedence.
Tables in the namespace sys cannot be merged into.
At least one of the three MATCHED
/ NOT MATCHED
clauses must be specified, but they can be specified in any order.
INSERT
, UPDATE
, or DELETE
actions specified on <target-table>
are limited by any constraints defined on it (when it is a base <table>
), including unique indices and any cascading referential integrity constraints.
It <target-table>
is updated in place, or a <new-table>
created, every INSERT
clause must account for all columns in <target-table>
. Inserting fewer columns results in a new row sub-type, which is allowed when creating a virtual <table-set>
.
Any <binary-operator>
referencing a column each from <target-table>
and <source-table>
satisfies the requirement that ON <merge-predicate>
not produce a cartesian join. However, it is to be noted a cartestian join cannot be entirely prevented depending on column contents.
Produced Metadata
@@ROWCOUNT
returns the total number of rows [inserted=@ud updated=@ud deleted=@ud].
Exceptions
<target-table>
does not exist
GRANT
permission on <target-table>
violated
<source-table>
does not exist
GRANT
permission on <source-table>
violated
<new-table>
already exists
referential integrity violation on the updated <target-table>
unique key violation
-- for updateable <target-table>
unique key violation is a violation of the primary index or any other unique index defined on the table
-- for producing new base <table>
output, <target-table>
primary index determines unique key violations
-- for producing PASS-THRU
output, columns referenced in <merge-predicate>
determine unique key violations for row sub-types that include all of the referenced columns
-- for producing PASS-THRU
output when <merge-predicate>
is not present, or the produced row sub-type does not include all of the referenced columns, the entire row by value determines unique key violations