urQL/docs/11-merge.md

# 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