Thanks for your response Rami - it worked! (Now that I think of it again it does make sense to include the key columns in the MERGE and SELECT portion. Otherwise it wouldn't know what to put into these columns in case of an INSERT.)
On Nov 17, 3:30 pm, Rami Ojares <[email protected]> wrote: > I think you should include the key columns in the column list > > MERGE INTO existingCustomers(l_id, b_id, name) KEY(l_id, b_id) > select l_id, b_id, name from newCustomers > > I would not label this as a bug. > While these columns could be inferred from the KEY(...) list specifying > them explicitly > does leave more room for flexibility eg. > > MERGE INTO existingCustomers(l_id, b_id, name) KEY(l_id, b_id) > select foo as l_id, bar as b_id, name from newCustomers > > Maybe it would be a nice to have feature that if H2 does not find a KEY > column either in the column list of the merge target or merge source it > would add them there. > I don't know ... a service for the lazy typist. > > So to answer your question in the title: incorrect usage. > > - rami > > 17.11.2010 15:52, Jens Helweg kirjoitti: > > > I am having a problem with MERGE and I think that I found a post in > > this group that relates to the same problem: > > >http://groups.google.com/group/h2-database/browse_frm/thread/727560ce... > > > (I can't reply to the post directly but do not know why. That's why I > > created a new post.) > > > I have created a test case with a couple of SQLs: > > > create table existingCustomers(l_id integer, b_id integer, name > > varchar(255)) > > create table newCustomers(l_id integer, b_id integer, name > > varchar(255)) > > > insert into existingCustomers values (1, 1001, 'Herbert') > > insert into existingCustomers values (2, 1001, 'Ronald') > > insert into existingCustomers values (3, 1001, 'Lisa') > > insert into existingCustomers values (4, 1002, 'Donald') > > insert into existingCustomers values (5, 1002, 'Ozzy') > > > insert into newCustomers values (1, 1001, 'Herbert Hawkins') > > insert into newCustomers values (2, 1001, 'Ronald McDonald') > > insert into newCustomers values (6, 1001, 'Markus McAttkins') > > > MERGE INTO existingCustomers(name) KEY(l_id, b_id) select name from > > newCustomers > > > Now when I run the last MERGE statement I am getting the error > > message: > > Column "L_ID" contains null values; > > > I am using the MERGE Statement incorrectly or is there a problem in > > the h2 db? > > > Btw, I am using H2 Version 1.2.143 > > > Thanks& Regards, > > Jens > > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
