Mixing
HANA SQL Script INSERT INTO with INNER JOIN can't insert values
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. :(
inner-join sql-insert hana-sql-script
asked Nov 20 '18 at 11:34
Laura JaneLaura Jane
11
add a comment |
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. :(
inner-join sql-insert hana-sql-script
asked Nov 20 '18 at 11:34
Laura JaneLaura Jane
11
INSERTis for adding new row, seems that you want to useUPDATE. This error says that you're trying to insert row without value forOrder_IDcolumn (which doesn't acceptNULLs)
– barbsan
Nov 20 '18 at 11:45
Thanks a lot for clarifying this to me, i did not know that :) I tried to do it withupdatebut it seems like I keep making mistakes.
– Laura Jane
Nov 20 '18 at 11:56
add a comment |
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. :(
inner-join sql-insert hana-sql-script
asked Nov 20 '18 at 11:34
Laura JaneLaura Jane
11
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. :(
inner-join sql-insert hana-sql-script
inner-join sql-insert hana-sql-script
asked Nov 20 '18 at 11:34
Laura JaneLaura Jane
11
asked Nov 20 '18 at 11:34
Laura JaneLaura Jane
11
edited Nov 20 '18 at 12:01
Laura Jane
asked Nov 20 '18 at 11:34
Laura JaneLaura Jane
11
asked Nov 20 '18 at 11:34
Laura JaneLaura Jane
11
asked Nov 20 '18 at 11:34
Laura JaneLaura Jane
11
11
INSERTis for adding new row, seems that you want to useUPDATE. This error says that you're trying to insert row without value forOrder_IDcolumn (which doesn't acceptNULLs)
– barbsan
Nov 20 '18 at 11:45
Thanks a lot for clarifying this to me, i did not know that :) I tried to do it withupdatebut it seems like I keep making mistakes.
– Laura Jane
Nov 20 '18 at 11:56
add a comment |
INSERTis for adding new row, seems that you want to useUPDATE. This error says that you're trying to insert row without value forOrder_IDcolumn (which doesn't acceptNULLs)
– barbsan
Nov 20 '18 at 11:45
Thanks a lot for clarifying this to me, i did not know that :) I tried to do it withupdatebut it seems like I keep making mistakes.
– Laura Jane
Nov 20 '18 at 11:56
INSERT is for adding new row, seems that you want to use UPDATE. This error says that you're trying to insert row without value for Order_ID column (which doesn't accept NULLs)– barbsan
Nov 20 '18 at 11:45
INSERT is for adding new row, seems that you want to use UPDATE. This error says that you're trying to insert row without value for Order_ID column (which doesn't accept NULLs)– barbsan
Nov 20 '18 at 11:45
Thanks a lot for clarifying this to me, i did not know that :) I tried to do it with
update but it seems like I keep making mistakes.– Laura Jane
Nov 20 '18 at 11:56
Thanks a lot for clarifying this to me, i did not know that :) I tried to do it with
update but it seems like I keep making mistakes.– Laura Jane
Nov 20 '18 at 11:56
add a comment |
1 Answer
1
active
oldest
votes
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
answered Dec 18 '18 at 11:00
EralperEralper
5,17011220
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53392132%2fhana-sql-script-insert-into-with-inner-join-cant-insert-values%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
answered Dec 18 '18 at 11:00
EralperEralper
5,17011220
add a comment |
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
answered Dec 18 '18 at 11:00
EralperEralper
5,17011220
add a comment |
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
answered Dec 18 '18 at 11:00
EralperEralper
5,17011220
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
answered Dec 18 '18 at 11:00
EralperEralper
5,17011220
answered Dec 18 '18 at 11:00
EralperEralper
5,17011220
answered Dec 18 '18 at 11:00
EralperEralper
5,17011220
answered Dec 18 '18 at 11:00
EralperEralper
5,17011220
5,17011220
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53392132%2fhana-sql-script-insert-into-with-inner-join-cant-insert-values%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
INSERTis for adding new row, seems that you want to useUPDATE. This error says that you're trying to insert row without value forOrder_IDcolumn (which doesn't acceptNULLs)– barbsan
Nov 20 '18 at 11:45
Thanks a lot for clarifying this to me, i did not know that :) I tried to do it with
updatebut it seems like I keep making mistakes.– Laura Jane
Nov 20 '18 at 11:56