1

Merge query in H2 is showing error that null is not allowed.

I have an apple table with 4 columns : id,type,name,status...where id and type cannot be null (ID being the primary key).

I am merging data of two columns "name , status" using merge command...

Merge Into Apple(ID,Name,status)
key (ID)
select ID,Name,Status from Fruit ;

It gives an error showing null is not allowed for column "type".

I dont know where it is going wrong.I have just one entry in apple table which contains a valid "id" and "type" already.

My fruits table does not have a type column and neither am I including it in the query then why is it showing problem with that particular column???

Armance
  • 5,350
  • 14
  • 57
  • 80
Abhishek Nigam
  • 11
  • 1

2 Answers2

1

The table Apple expects type to have some kind of value other than null and this is not delivered by your selection from Fruit. This is where it goes wrong. I don't know h2, but maybe the statement should look somehow like: Merge Into Apple(ID,Name,status, type) key (ID) select ID,Name,Status,0 from Fruit ;

So type would just be 0 for every Apple.

bouscher
  • 1,300
  • 1
  • 9
  • 18
  • actually merge allows us to add column specific values without disturbing other columns.Now when i add into apple table row which already has a type present for it then by providing a value of 0 the purpose will be defeated.....I want to append 2 columns to the table apple from fruit with the values from fruit keeping all the previous data in apple intact.....Can you suggest something else... – Abhishek Nigam May 10 '13 at 11:35
  • How about: Merge Into Apple KEY(ID) select Name,Status from Fruit ; – bouscher May 10 '13 at 11:39
  • That's because the number of columns in Fruit is smaller than the number of columns in Apple, which is why I suggested to give a default value like 0 for the column type, since it expects some kind of value. As long as you just append rows, there is really no problem with this default value. – bouscher May 10 '13 at 11:52
  • [link](http://stackoverflow.com/questions/13716105/oracle-merge-statement-in-h2-database) i hope this will help you understand the merge query for h2 db and that adding 0 would not help because i don't have the corresponding column in the fruit table....i can add only those columns which are there in fruit table to the apple table....specifying 0 gives me a syntax error as i checked it as a precaution.... – Abhishek Nigam May 10 '13 at 12:02
  • The easiest way would be to add a type column to fruit, so that the number of columns is identical. As long as you do not provide a value for type h2 will automatically provide the value null for that column and this will continue to give you the error. – bouscher May 10 '13 at 12:20
  • @bouscher The SQL statement in your answer is not legal in H2, because 0 is not a column name. – Thomas Mueller May 13 '13 at 08:02
  • @ThomasMueller That's why I wrote "maybe the statement should look somehow like...". I should have put this into a comment, not an answer, the 0 should have been on the SELECT side, obviously. – bouscher May 13 '13 at 09:39
  • @bouscher Thanks! Yes, that makes sense. I should probably not have added an answer, yours is fine. – Thomas Mueller May 13 '13 at 10:11
  • @ThomasMueller Not at all, yours was the better answer, since you actually seem to know H2 syntax. But the question remains, if this is the desired solution, since the statement might also update existing entries with an type value of 0, which is unwanted, am I right? – bouscher May 13 '13 at 10:21
  • @bouscher I wrote the merge implementation so I whould know the syntax :-) If updating the value isn't desired, then a default value should defined for this column when creating the table. – Thomas Mueller May 13 '13 at 10:35
  • @ThomasMueller Entschuldige bitte, ich hab nicht kapiert, dass du DER Thomas Müller bist. Ich schätze, ich bin dann einfach mal ruhig. – bouscher May 13 '13 at 10:45
1

If the table contains a column type that can not be null (as you wrote), then you have to provide a value for this column in the statement:

Merge Into Apple(ID,Name,status,type)
key (ID)
select ID,Name,Status,0 from Fruit ;

As an alternative, you can specify a default value when creating the table.

Thomas Mueller
  • 48,905
  • 14
  • 116
  • 132