Hi, Rami it's right. You can use: MERGE INTO existingCustomers key (l_id, b_id) select * from newCustomers; MERGE INTO existingCustomers (l_id, b_id, name) key (l_id, b_id) select l_id, b_id, name from newCustomers;
But not: MERGE INTO existingCustomers (name) key (l_id, b_id) select name from newCustomers; You are*explicitly **restricting * the column list from the input result set, so the others columns are taken as null values and then H2 complains with: "*Error: Column "L_ID" contains null values*" regards, Dario. El 17/11/10 11:30, Rami Ojares escribió: > 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/727560ce01c04a54/039121e1477fac6b?lnk=gst&q=merge+contains+null#039121e1477fac6b >> >> (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.
