db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-3155) Support for SQL:2003 MERGE statement
Date Fri, 07 Feb 2014 14:41:28 GMT

    [ https://issues.apache.org/jira/browse/DERBY-3155?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13894574#comment-13894574
] 

Knut Anders Hatlen commented on DERBY-3155:
-------------------------------------------

The syntax description in the functional specification says that the source table and the
target table can have a correlation name. It doesn't seem to allow column lists to go with
the correlation names (since it says correlationName, not correlationClause). The actually
implemented syntax seems to accept column lists, though, and the column names can be used
in the ON clause:

{noformat}
ij> create table t(x int);
0 rows inserted/updated/deleted
ij> merge into t t1(a) using t t2(b) on a=b when not matched then insert values (1);
0 rows inserted/updated/deleted
WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an
empty table.
{noformat}

However, the specified column name cannot be used in the mergeInsert clause:

{noformat}
ij> merge into t t1(a) using t t2(b) on a=b when not matched then insert values (b);
ERROR 42X04: Column 'B' is either not in any table in the FROM list or appears within a join
specification and is outside the scope of the join specification or appears in a HAVING clause
and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'B' is
not a column in the target table.
{noformat}

If I read the SQL standard correctly, it doesn't allow a column list for the target table.
It does seem to allow a column list for the source table, though.

> Support for SQL:2003 MERGE statement
> ------------------------------------
>
>                 Key: DERBY-3155
>                 URL: https://issues.apache.org/jira/browse/DERBY-3155
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>            Reporter: Trejkaz
>            Assignee: Rick Hillegas
>              Labels: derby_triage10_10
>         Attachments: MergeStatement.html, MergeStatement.html, MergeStatement.html, derby-3155-01-ac-grammar.diff,
derby-3155-02-ag-fixParserWarning.diff, derby-3155-03-ae-backingStoreHashtableWithRowLocation.diff,
derby-3155-03-af-backingStoreHashtableWithRowLocation.diff, derby-3155-03-ag-backingStoreHashtableWithRowLocation.diff,
derby-3155-03-ah-backingStoreHashtableWithRowLocation.diff, derby-3155-04-ae-deleteAction.diff,
derby-3155-04-af-deleteAction.diff, derby-3155-05-aa-triggerTransitionTableAsTarget.diff,
derby-3155-06-aa-triggerTransitionTableAsSource.diff, derby-3155-07-ad-insertAction.diff,
derby-3155-08-ah-updateAction.diff, derby-3155-09-aa-correlationNames.diff, derby-3155-10-aa-correlationNames.diff,
derby-3155-11-ab-beforeTriggersCantFireMerge.diff, derby-3155-12-aa-canOmitInsertColumnList.diff,
derby-3155-13-aa-allowSystemAndTempTables.diff, derby-3155-14-aa-replaceCorrelationNamesOnLeftSideOfSETclauses.diff,
derby-3155-15-aa-replumbMergeResultSetCleanup.diff, derby-3155-16-aa-treatCurrentRowLocationNodeLikeBaseColumnNode.diff,
derby-3155-17-aa-serializingRowLocations.diff, derby-3155-18-aa-basicView.diff, derby-3155-19-aa-forbidSubqueriesInMatchedClauses.diff,
derby-3155-20-aa-reworkColumnMatching.diff
>
>
> A relatively common piece of logic in a database application is to check for a row's
existence and then either update or insert depending on its existence.
> SQL:2003 added a MERGE statement to perform this operation.  It looks like this:
>     MERGE INTO table_name USING table_name ON (condition)
>     WHEN MATCHED THEN UPDATE SET column1 = value1 [, column2 = value2 ...]
>     WHEN NOT MATCHED THEN INSERT column1 [, column2 ...] VALUES (value1 [, value2 ...])

> At the moment, the only workaround for this would be to write a stored procedure to do
the same operation, or to implement the logic client-side.



--
This message was sent by Atlassian JIRA
(v6.1.5#6160)

Mime
View raw message