org.h2.command.dml

## Class MergeUsing

- java.lang.Object
- - org.h2.command.Prepared
  - - org.h2.command.dml.MergeUsing

    public class MergeUsing
    extends Prepared

This class represents the statement syntax MERGE table alias USING... It does not replace the existing MERGE INTO... KEYS... form. It supports the SQL 2003/2008 standard MERGE statement: http://en.wikipedia.org/wiki/Merge_%28SQL%29 Database management systems Oracle Database, DB2, Teradata, EXASOL, Firebird, CUBRID, HSQLDB, MS SQL, Vectorwise and Apache Derby & Postgres support the standard syntax of the SQL 2003/2008 MERGE command: MERGE INTO targetTable AS T USING sourceTable AS S ON (T.ID = S.ID) WHEN MATCHED THEN UPDATE SET column1 = value1 [, column2 = value2 ...] WHERE column1=valueUpdate DELETE WHERE column1=valueDelete WHEN NOT MATCHED THEN INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...]); Only Oracle support the additional optional DELETE clause. Implementation notes: 1) The ON clause must specify 1 or more columns from the TARGET table because they are used in the plan SQL WHERE statement. Otherwise an exception is raised. 2) The ON clause must specify 1 or more columns from the SOURCE table/query because they are used to track the join key values for every source table row - to prevent any TARGET rows from being updated twice per MERGE USING statement. This is to implement a requirement from the MERGE INTO specification requiring each row from being updated more than once per MERGE USING statement. The source columns are used to gather the effective "key" values which have been updated, in order to implement this requirement. If the no SOURCE table/query columns are found in the ON clause, then an exception is raised. The update row counts of the embedded UPDATE and DELETE statements are also tracked to ensure no more than 1 row is ever updated. (Note One special case of this is that the DELETE is allowed to affect the same row which was updated by UPDATE - this is an Oracle only extension.) 3) UPDATE and DELETE statements are allowed to specify extra conditional criteria (in the WHERE clause) to allow fine-grained control of actions when a record is found. The ON clause conditions are always prepended to the WHERE clause of these embedded statements, so they will never update more than the ON join condition. 4) Previously if neither UPDATE or DELETE clause is supplied, but INSERT is supplied - the INSERT action is always triggered. This is because the embedded UPDATE and DELETE statement's returned update row count is used to detect a matching join. If neither of the two the statements are provided, no matching join is NEVER detected. A fix for this is now implemented as described below: We now generate a "matchSelect" query and use that to always detect a match join - rather than relying on UPDATE or DELETE statements. This is an improvement, especially in the case that if either of the UPDATE or DELETE statements had their own fine-grained WHERE conditions, making them completely different conditions than the plain ON condition clause which the SQL author would be specifying/expecting. An additional benefit of this solution is that this "matchSelect" query is used to return the ROWID of the found (or inserted) query - for more accurate enforcing of the only-update-each-target-row-once rule.

### Field Summary

### Fields inherited from class org.h2.command.Prepared

`create, parameters, prepareAlways, session, sqlStatement`

### Constructor Summary

Constructors
Constructor and Description
`MergeUsing(Merge merge)`

### Method Summary

Methods
`Prepared` `getDeleteCommand()`
`Insert` `getInsertCommand()`
`Expression` `getOnCondition()`
`java.lang.String` `getPlanSQL()` Get the SQL statement with the execution plan.
`Query` `getQuery()`
`java.lang.String` `getQueryAlias()`
`TableFilter` `getSourceTableFilter()`
`Select` `getTargetMatchQuery()`
`Table` `getTargetTable()`
`TableFilter` `getTargetTableFilter()`
`int` `getType()` Get the command type as defined in CommandInterface
`Prepared` `getUpdateCommand()`
`boolean` `isTransactional()` Check if this command is transactional.
`protected void` `merge(Row sourceRow)` Merge the given row.
`void` `prepare()` Prepare this statement.
`ResultInterface` `queryMeta()` Get an empty result set containing the meta data.
`void` `setDeleteCommand(Delete deleteCommand)`
`void` `setInsertCommand(Insert insertCommand)`
`void` `setOnCondition(Expression condition)`
`void` `setQuery(Query query)`
`void` `setQueryAlias(java.lang.String alias)`
`void` `setSourceTableFilter(TableFilter sourceTableFilter)`
`void` `setTargetMatchQuery(Select targetMatchQuery)`
`void` `setTargetTable(Table targetTable)`
`void` `setTargetTableFilter(TableFilter targetTableFilter)`
`void` `setUpdateCommand(Update updateCommand)`
`int` `update()` Execute the statement.

### Methods inherited from class org.h2.command.Prepared

`checkCanceled, checkParameters, getCteCleanups, getCurrentObjectId, getCurrentRowNumber, getObjectId, getParameters, getSession, getSQL, getSQL, getSQL, isCacheable, isQuery, isReadOnly, needRecompile, query, setCommand, setCteCleanups, setCurrentRowNumber, setObjectId, setParameterList, setPrepareAlways, setRow, setSession, setSQL, toString`

### Methods inherited from class java.lang.Object

`clone, equals, finalize, getClass, hashCode, notify, notifyAll, wait, wait, wait`

### Constructor Detail

#### MergeUsing

    public MergeUsing(Merge merge)

### Method Detail

#### update

    public int update()

Description copied from class: `Prepared`
Execute the statement.

Overrides:
  `update` in class `Prepared`
Returns:
  the update count

#### merge

    protected void merge(Row sourceRow)

Merge the given row.

Parameters:
  `sourceRow` - the row

#### getPlanSQL

    public java.lang.String getPlanSQL()

Description copied from class: `Prepared`
Get the SQL statement with the execution plan.

Overrides:
  `getPlanSQL` in class `Prepared`
Returns:
  the execution plan

#### prepare

    public void prepare()

Description copied from class: `Prepared`
Prepare this statement.

Overrides:
  `prepare` in class `Prepared`

#### setSourceTableFilter

    public void setSourceTableFilter(TableFilter sourceTableFilter)

#### getSourceTableFilter

    public TableFilter getSourceTableFilter()

#### setOnCondition

    public void setOnCondition(Expression condition)

#### getOnCondition

    public Expression getOnCondition()

#### getUpdateCommand

    public Prepared getUpdateCommand()

#### setUpdateCommand

    public void setUpdateCommand(Update updateCommand)

#### getDeleteCommand

    public Prepared getDeleteCommand()

#### setDeleteCommand

    public void setDeleteCommand(Delete deleteCommand)

#### getInsertCommand

    public Insert getInsertCommand()

#### setInsertCommand

    public void setInsertCommand(Insert insertCommand)

#### setQueryAlias

    public void setQueryAlias(java.lang.String alias)

#### getQueryAlias

    public java.lang.String getQueryAlias()

#### getQuery

    public Query getQuery()

#### setQuery

    public void setQuery(Query query)

#### setTargetTableFilter

    public void setTargetTableFilter(TableFilter targetTableFilter)

#### getTargetTableFilter

    public TableFilter getTargetTableFilter()

#### getTargetTable

    public Table getTargetTable()

#### setTargetTable

    public void setTargetTable(Table targetTable)

#### getTargetMatchQuery

    public Select getTargetMatchQuery()

#### setTargetMatchQuery

    public void setTargetMatchQuery(Select targetMatchQuery)

#### isTransactional

    public boolean isTransactional()

Description copied from class: `Prepared`
Check if this command is transactional. If it is not, then it forces the current transaction to commit.

Specified by:
  `isTransactional` in class `Prepared`
Returns:
  true if it is

#### queryMeta

    public ResultInterface queryMeta()

Description copied from class: `Prepared`
Get an empty result set containing the meta data.

Specified by:
  `queryMeta` in class `Prepared`
Returns:
  the result set

#### getType

    public int getType()

Description copied from class: `Prepared`
Get the command type as defined in CommandInterface

Specified by:
  `getType` in class `Prepared`
Returns:
  the statement type