db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] [Comment Edited] (DERBY-3155) Support for SQL:2003 MERGE statement
Date Tue, 14 Jan 2014 20:27:19 GMT

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

Rick Hillegas edited comment on DERBY-3155 at 1/14/14 8:25 PM:
---------------------------------------------------------------

Hi Rick, 
it seems that if you have a {{targetCorrelationName}} that's different from {{targetTable}}
you get an error if you're referring to {{targetCorrelationName}} in a {{mergeWhenClause}}.
Using it in {{searchCondition}} is ok:

{quote}
Exception in thread "main" java.sql.SQLSyntaxErrorException: Table name 'M' should be the
same as 'META'.
	at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:93)
	at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:288)
	at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:424)
	at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:353)
	at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2396)
	at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:82)
	at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:691)
	at org.apache.derby.impl.jdbc.EmbedStatement.executeLargeUpdate(EmbedStatement.java:181)
	at org.apache.derby.impl.jdbc.EmbedStatement.executeUpdate(EmbedStatement.java:170)
	at regexp.Driver.main(Driver.java:133)
Caused by: java.sql.SQLException: Table name 'M' should be the same as 'META'.
	at org.apache.derby.impl.jdbc.SQLExceptionFactory.wrapArgsForTransportAcrossDRDA(SQLExceptionFactory.java:138)
	at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:73)
	... 9 more
Caused by: ERROR 42X55: Table name 'M' should be the same as 'META'.
	at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:265)
	at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:260)
	at org.apache.derby.impl.sql.compile.ResultColumn.setColumnDescriptor(ResultColumn.java:915)
	at org.apache.derby.impl.sql.compile.ResultColumn.bindResultColumnByName(ResultColumn.java:834)
	at org.apache.derby.impl.sql.compile.ResultColumnList.bindResultColumnsByName(ResultColumnList.java:890)
	at org.apache.derby.impl.sql.compile.InsertNode.bindStatement(InsertNode.java:282)
	at org.apache.derby.impl.sql.compile.MatchingClauseNode.bindInsert(MatchingClauseNode.java:770)
	at org.apache.derby.impl.sql.compile.MatchingClauseNode.bind(MatchingClauseNode.java:224)
	at org.apache.derby.impl.sql.compile.MergeNode.bindStatement(MergeNode.java:258)
	at org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:402)
	at org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:99)
	at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConnectionContext.java:1116)
	at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:682)
	... 3 more
{quote}

Here is a script for this problem:

{noformat}
connect 'jdbc:derby:memory:db;create=true';

create table t1( a int, b int );
create table t2( c int, d int );

insert into t1 values ( 1, 100 ), ( 2, 200 );
insert into t2 values ( 1, 100 ), (2, 200), ( 3, 300 );

merge into t1 x
using t2 y on x.a = y.a
when matched and x.b > 100 then update set x.b = y.d;
{noformat}



was (Author: dyret):
Hi Rick, 
it seems that if you have a {{targetCorrelationName}} that's different from {{targetTable}}
you get an error if you're referring to {{targetCorrelationName}} in a {{mergeWhenClause}}.
Using it in {{searchCondition}} is ok:

{quote}
Exception in thread "main" java.sql.SQLSyntaxErrorException: Table name 'M' should be the
same as 'META'.
	at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:93)
	at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:288)
	at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:424)
	at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:353)
	at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2396)
	at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:82)
	at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:691)
	at org.apache.derby.impl.jdbc.EmbedStatement.executeLargeUpdate(EmbedStatement.java:181)
	at org.apache.derby.impl.jdbc.EmbedStatement.executeUpdate(EmbedStatement.java:170)
	at regexp.Driver.main(Driver.java:133)
Caused by: java.sql.SQLException: Table name 'M' should be the same as 'META'.
	at org.apache.derby.impl.jdbc.SQLExceptionFactory.wrapArgsForTransportAcrossDRDA(SQLExceptionFactory.java:138)
	at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:73)
	... 9 more
Caused by: ERROR 42X55: Table name 'M' should be the same as 'META'.
	at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:265)
	at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:260)
	at org.apache.derby.impl.sql.compile.ResultColumn.setColumnDescriptor(ResultColumn.java:915)
	at org.apache.derby.impl.sql.compile.ResultColumn.bindResultColumnByName(ResultColumn.java:834)
	at org.apache.derby.impl.sql.compile.ResultColumnList.bindResultColumnsByName(ResultColumnList.java:890)
	at org.apache.derby.impl.sql.compile.InsertNode.bindStatement(InsertNode.java:282)
	at org.apache.derby.impl.sql.compile.MatchingClauseNode.bindInsert(MatchingClauseNode.java:770)
	at org.apache.derby.impl.sql.compile.MatchingClauseNode.bind(MatchingClauseNode.java:224)
	at org.apache.derby.impl.sql.compile.MergeNode.bindStatement(MergeNode.java:258)
	at org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:402)
	at org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:99)
	at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConnectionContext.java:1116)
	at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:682)
	... 3 more
{quote}

> 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
>
>
> 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