HANA SQL Script INSERT INTO with INNER JOIN can't insert values

Question

I'm quite new to SQL and hope you can help me with my problem.

I have a table called Order_Status_Form_3 with the columns Order_ID (KEY), Customer_ID, Customer_Unique_ID, Status(KEY) and Date.

The table is filled, except for the Customer_Unique_ID Column.

To fill this Column I need to reference the Customer table where the Customer_ID is linked to the Customer_Unique_ID, so the right IDs cover the right places. When the Customer_ID in Order_Status_Form_3 equals the Customer_ID in the Customer table the given Customer_Unique_ID shall be inserted into the Customer_Unique_ID column in Order_Status_Form_3.

I tried to combine an INSERT INTO with a SELECT and INNER JOIN, but received an error message that says:

"cannot insert NULL or update to NULL: Order_ID".

I guess it's not clear for the program where to insert the values found and it tries to insert into all columns. I searched for similar problems but could not find any satisfying answers for my specific problem.

Here's the code I used:

Insert Into "HXE_109"."Order_Status_Form_3" ("Customer_Unique_ID") 
Select customer."customer_unique_id"
From "HXE_109"."Customer" As customer
Inner Join "HXE_109"."Order_Status_Form_3" As OrderStatus3
On OrderStatus3."Customer_ID" = customer."customer_id"

I tried to specify the place to insert the values found by attaching a WHERE at the end, but received the same error.

Where OrderStatus3."Customer_ID" = customer."customer_id"

Does anyone know how to solve this issue and can tell me where my mistake is?

Thanks in advance for reading this long question and leaving an answer.

Edit

I tried using update but it seems like I cannot get it right.

Update "HXE_109"."Order_Status_Form_3" Set "Customer_Unique_ID" = (Select customer."customer_unique_id" From "HXE_109"."Customer" As customer Inner Join "HXE_109"."Order_Status_Form_3" As OrderStatus3 On OrderStatus3."Customer_ID" = customer."customer_id") Now I get the following error:

single row query returns more than one row

Do I need to use a Where condition here?

Sorry for my stupidity. :(

Answer 1

As I could follow the comments, what you want to do is running an UPDATE statement

Please check following DML command

Update "HXE_109"."Order_Status_Form_3" 
Set 
    "Customer_Unique_ID" = customer."customer_unique_id"
From "HXE_109"."Order_Status_Form_3" As OrderStatus3
Inner Join "HXE_109"."Customer" As customer
    On OrderStatus3."Customer_ID" = customer."customer_id"

If I try to explain the error messages:

"cannot insert NULL or update to NULL: Order_ID".

That is related with a field defined as NOT NULL. So in your target table ORDER_ID is defined as "not null", so in INSERT you have to provide a value for it or define it as an identity field in your HANA table definition

The second error: single row query returns more than one row

This is related with the case where SQL Engine expects a value not a set of values. So the SELECT statement that you assign the results to Customer_Unique_ID field returns more than 1 value. In this case SQL engine raises an exception