search

lbehnke / h2database

Automatically exported from code.google.com/p/h2database
0 stars 0 forks source link

Merge does not work as expected #194

Closed GoogleCodeExporter closed 9 years ago

GoogleCodeExporter commented 9 years ago 
Before submitting a bug, please check the FAQ:
http://www.h2database.com/html/faq.html

What steps will reproduce the problem?
 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);

What is the expected output? What do you see instead?
I expect the two rows in test_ab to be updated. Instead, I get the error
Unique index or primary key violation: "PUBLIC.TEST_AB"; SQL
statement: 
This makes no sense to me. I expect that with each match in the join clause
the respective row is updated.

What version of the product are you using? On what operating system, file
system, and virtual machine?
1.2.134.
Winxp 64
ntfs 
jdk 1.6

Do you know a workaround?
No.

How important/urgent is the problem for you?
Very. We are getting update-join to work on all platforms (sql
server,oracle,db2,h2) and so far only H2 is not working.

In your view, is this a defect or a feature request?
It's a defect in that it's either a bug or is working as designed. But if
working as designed it is not well enough documented to understand how it
works.

Please provide any additional information below.
Thanks for looking at it!
Randy

Original issue reported on code.google.com by youn...@gmail.com on 29 Apr 2010 at 6:37

GoogleCodeExporter commented 9 years ago 
> 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.

Original comment by thomas.t...@gmail.com on 2 May 2010 at 1:09