CODEWITHSUNDEEP
databasemergeh2
Abhishek Nigam
Abhishek Nigam

Reputation: 11

Merge query in H2 db showing null not allowed error

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???

Upvotes: 1

Views: 837

Answers (2)

bouscher
bouscher

Reputation: 1360

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.

Upvotes: 1

Thomas Mueller
Thomas Mueller

Reputation: 50087

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.

Upvotes: 1

Related Questions