Hitting error "cannot insert NULL or update to NULL"

[Former Member]

Hi Experts,

I am hitting the error "cannot insert NULL or update to NULL" when doing update sql in hana.

I want to update HOUSE_NO field to NULL if condition meet as following:

UPDATE schema.test

SET HOUSE_NO = NULL WHERE HOUSE_NO = '<>'

Following is the Fuzzy table definition:

CREATE COLUMN TABLE schema.test(                                

"CLIENT"    VARCHAR(3) DEFAULT '001' NOT NULL,            

"GUID"      VARBINARY(16) CS_RAW NOT NULL,                                   

"CITY"      TEXT FUZZY SEARCH INDEX ON FAST PREPROCESS ON,

"COUNTRY"   TEXT FUZZY SEARCH INDEX ON FAST PREPROCESS ON,

"HOUSE_NO"  TEXT FUZZY SEARCH INDEX ON FAST PREPROCESS ON,

PRIMARY KEY ("CLIENT","GUID"))

Hope someone can let me know what i did wrong?

Thanks & Regards,

Alexender

[former_member751591]

Thanks for coming to SAP Community for answers. Please post your question as a new question here:

Since you're new in asking questions here, check out our tutorial about asking and answering questions (if you haven't already), as it provides tips for preparing questions more effectively, that draw responses from our members.

Please note, that your post here won't be answered.

[former_member184768 · Accepted solution]

Hi Alexender,

I tried to simulate the error, but I could update the column. I could not update it with the where condition using = operator, but did it with LIKE operator.

Can you please check the following:

1) The column HOUSE_NO is NOT "not null". ( I don't think this could be the case considering your create table statement).

2) Can you please check if the table test has foreign key relationship as master table. That means the columns CLINET and GUID might have been referred by some other table in foreign key relationship. If this is the case, HANA will not allow you to update the data in the test table to null.

Regards,

Ravi

[Former Member · Accepted solution]

Hi Ravi,

But HOUSE_NO is not a Key field in this case, does it matter if the Key fields (CLIENT and GUID) as foreign key by other table?

I have tried update with LIKE operator and i still got the same error.

Regards,

Alexender

[Former Member · Accepted solution]

Hi Ravi,

I have solve the issue, in order to update fuzzy field to NULL, in create table definition must set the field as DEFAULT NULL as following:

"HOUSE_NO"  TEXT FUZZY SEARCH INDEX ON FAST PREPROCESS ON DEFAULT NULL

Then, we can simply update the fuzzy field to NULL if condition meet:

UPDATE schema.test

SET HOUSE_NO = NULL WHERE HOUSE_NO LIKE '<NULL>'

Please note that cannot set where condition as WHERE HOUSE_NO = '<NULL>', otherwise you will get error "inconsistent datatype - lob type comparison".

Regards,

Alexender

[former_member952861]

I had this same problem, at first I tried to check the errors but there was none. The records that were accepted were same in structure to the once getting the NULL error.

I copied the contents of the CSV file to another file and then tried to import which resolved the problem.