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












0















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. :(










share|improve this question

























  • 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


















0















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. :(










share|improve this question

























  • 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
















0












0








0








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. :(










share|improve this 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. :(







inner-join sql-insert hana-sql-script






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 20 '18 at 12:01







Laura Jane

















asked Nov 20 '18 at 11:34









Laura JaneLaura Jane

11




11













  • 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





















  • 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



















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














1 Answer
1






active

oldest

votes


















0














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






share|improve this answer























    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
    });


    }
    });














    draft saved

    draft discarded


















    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









    0














    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






    share|improve this answer




























      0














      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






      share|improve this answer


























        0












        0








        0







        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






        share|improve this answer













        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







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Dec 18 '18 at 11:00









        EralperEralper

        5,24011221




        5,24011221
































            draft saved

            draft discarded




















































            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.




            draft saved


            draft discarded














            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





















































            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







            Popular posts from this blog

            Guess what letter conforming each word

            Port of Spain

            Run scheduled task as local user group (not BUILTIN)