Merge is not behaving the way I expect. Bug?

41 views
Skip to first unread message

SuperSpinner

unread,
Apr 28, 2010, 12:33:58 AM4/28/10
to H2 Database
H2 1.1.112

CREATE TABLE
TEST_A( A INTEGER, PUBLICID VARCHAR(20) NOT NULL, ID INTEGER NOT
NULL);

CREATE TABLE
TEST_AB( AB INTEGER, AID INTEGER, PUBLICID VARCHAR(20) NOT NULL, ID
INTEGER NOT NULL);

INSERT INTO TEST_A(A, PUBLICID, ID)
VALUES(11, '1', 1);

INSERT INTO TEST_AB(AB, AID, PUBLICID, ID)
VALUES(31, 1, '1', 1),(32, 1, '2', 2);

MERGE INTO test_ab (AID, AB) KEY (AID)
SELECT test_ab.AID, (test_ab.AB + 2000) FROM test_ab test_ab
INNER JOIN test_a test_a
ON (test_a.ID = test_ab.AID AND test_a.A = 11);

select * from test_a;
select * from test_ab;

I want MERGE to update AB but it fails because it apparently is trying
to merge 2 rows into one row. Why doesn't
it iteratively find each row and update AB for each row? That's what I
want.

Thanks!
Randy

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

SuperSpinner

unread,
Apr 28, 2010, 6:29:18 PM4/28/10
to H2 Database
maybe the failure wasn't clear. I get the following.

Unique index or primary key violation: "PUBLIC.TEST_AB"; SQL
statement:
MERGE INTO test_ab (AID, AB) KEY (AID)
SELECT test_ab.AID, (test_ab.AB + 2000) FROM test_ab test_ab
INNER JOIN test_a test_a
ON (test_a.ID = test_ab.AID AND test_a.A = 11) [23001-134] 23001/23001
(Help)
org.h2.jdbc.JdbcSQLException: Unique index or primary key violation:
"PUBLIC.TEST_AB"; SQL statement:
MERGE INTO test_ab (AID, AB) KEY (AID)
SELECT test_ab.AID, (test_ab.AB + 2000) FROM test_ab test_ab
INNER JOIN test_a test_a
ON (test_a.ID = test_ab.AID AND test_a.A = 11) [23001-134]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:
316)
at org.h2.message.DbException.get(DbException.java:167)
at org.h2.message.DbException.get(DbException.java:144)
at org.h2.command.dml.Merge.merge(Merge.java:172)
at org.h2.command.dml.Merge.update(Merge.java:128)
at org.h2.command.CommandContainer.update(CommandContainer.java:
70)
at org.h2.command.Command.executeUpdate(Command.java:198)
at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:
176)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:151)
at org.h2.server.web.WebApp.getResult(WebApp.java:1260)
at org.h2.server.web.WebApp.query(WebApp.java:954)
at org.h2.server.web.WebApp.process(WebApp.java:216)
at org.h2.server.web.WebApp.processRequest(WebApp.java:163)
at org.h2.server.web.WebThread.process(WebThread.java:129)
at org.h2.server.web.WebThread.run(WebThread.java:86)
at java.lang.Thread.run(Thread.java:619)

Thomas Mueller

unread,
May 1, 2010, 9:06:00 AM5/1/10
to h2-da...@googlegroups.com
Hi,

> I want MERGE to update AB but it fails because
> it apparently is trying to merge 2 rows into one row.

No, it is trying to merge one row into two rows: before merging, there
are two rows in test_ab with aid = 1. Therefore, aid can't be used as
the key.

Regards,
Thomas

SuperSpinner

unread,
May 3, 2010, 4:10:38 PM5/3/10
to H2 Database
Hi Thomas,
Thanks for the reply. What I'm trying to accomplish is this:
While I didn't create primary key/foreign key relationships in the
example, my real life use case has them.
Given that test_a.id is a primary key and test_ab.aid is a foreign key
pointing to test_a.id,
I want to update the two rows in test_ab that point to the test_a row
where test_a = 11

It's possible to write a merge or insert/join type of query on both
oracle and sql server to do this, but I haven't been able to figure
out
how to write a query in H2 to accomplish the same thing.

something like
update test_ab set test_ab.ab = test_ab.ab + 2000 from
test_a,test_ab where test_ab.aid = test_a.id and test_a.a=11

Is it possible?

thanks
Randy

On May 1, 6:06 am, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

Brian

unread,
May 4, 2010, 12:18:12 AM5/4/10
to H2 Database
What I generally do in these situations is to make the source - a join
which includes the primary key of the target table, which can help
guarantee uniqueness for the MERGE and still get the desired results.
This frustrated the heck out of me at first because I was used to
working with MERGE syntax from Oracle/SQL - which pretty much did that
for you. Once I got the hang of it - it's not bad at all.

Hope that Helps,
-Brian

Thomas Mueller

unread,
May 5, 2010, 2:12:53 PM5/5/10
to h2-da...@googlegroups.com
Hi,

> What I'm trying to accomplish is this:
> While I didn't create primary key/foreign key relationships in the
> example, my real life use case has them.

Could you provide a simple example?

> Given that test_a.id is a primary key ...

> It's possible to write a merge or insert/join type of query on both
> oracle and sql server to do this, but I haven't been able to figure
> out how to write a query in H2 to accomplish the same thing.

I would prefer actual source code or SQL statements. If you have them,
that would be great.
Reply all
Reply to author
Forward
0 new messages