1 2 Previous Next 20 Replies Latest reply: Jan 31, 2013 4:39 PM by SomeoneElse Go to original post RSS
      • 15. Re: update recors
        sb92075
        I am confused by SQL below.
        The goal of the SQL is to SET A.AMOUNT.
        Right?
        but the WHERE clause is as below
        where a.amount = b.budgeted_amount
        which can only leave A.AMOUNT unchanged!
        966949 wrote:
        Can you please correct this query!!
        update forecast_task a set a.amount =
        (select b. budgeted_amount
        from project_task_budget b,
        forecast_info c
        where a.amount                    =  b.budgeted_amount
        and    a.project_task_id       =  b.project_task_id
        and    c.forecast_status_id  =  9);
        Getting below error.

        SQL Error: ORA-01427: single-row subquery returns more than one row
        01427. 00000 - "single-row subquery returns more than one row"
        *Cause:   
        *Action:

        Thanks.
        In order to avoid the error above the SQL below must only ever return 1 row.
        (select b. budgeted_amount
        from project_task_budget b,
        forecast_info c
        where a.amount = b.budgeted_amount
        and a.project_task_id = b.project_task_id
        and c.forecast_status_id = 9);
        • 16. Re: update recors
          969952
          Yes.. I am trying to update the amount .

          Please help me out and correct the query.
          • 17. Re: update recors
            969952
            find the below query.
            update forecast_task a set a.amount =
                (select b. budgeted_amount,c.forecast_status_id
                            from project_task_budget b,
                                     forecast_info c
                               where a.amount                    =  b.budgeted_amount
                                 and    a.project_task_id       =  b.project_task_id
                                 and    c.forecast_status_id  =  9); 
            Now getting the below error.

            SQL Error: ORA-00913: too many values
            00913. 00000 - "too many values"
            *Cause:   
            *Action:

            Thanks.
            • 18. Re: update recors
              sb92075
              966949 wrote:
              find the below query.
              update forecast_task a set a.amount =
              (select b. budgeted_amount,c.forecast_status_id
              from project_task_budget b,
              forecast_info c
              where a.amount                    =  b.budgeted_amount
              and    a.project_task_id       =  b.project_task_id
              and    c.forecast_status_id  =  9); 
              Now getting the below error.

              SQL Error: ORA-00913: too many values
              00913. 00000 - "too many values"
              *Cause:   
              *Action:

              Thanks.
              set a.amount =
              above expects/requires only a single value be assigned to it.
              select b. budgeted_amount,c.forecast_status_id
              above returns TWO values; so which is to be placed into A.AMOUNT?
              • 19. Re: update recors
                969952
                Hi,

                Modified the query as follows.
                update forecast_task a set a.amount =
                    (select a.amount,b. budgeted_amount,c.forecast_status_id
                                from forecast_task a,
                                         project_task_budget b,
                                         forecast_info c
                                   where a.amount                    =  b.budgeted_amount
                                     and    a.project_task_id       =  b.project_task_id
                                     and    c.forecast_status_id  =  9); 
                the select statement is returning 100 records. I am getting error like

                SQL Error: ORA-00913: too many values
                00913. 00000 - "too many values"
                *Cause:   
                *Action:

                Please have a look into the above query and suggest me.

                Thanks.
                • 20. Re: update recors
                  SomeoneElse
                  No, that won't work either. Now you have 3 tables in the subquery, one of which is the same as the table you're updating.

                  And because you've used the same alias, the subquery isn't correlated with the outer table.

                  I would suggest abandoning the update statement since it's not doing what you want and even if you got it working, it will update every row in forecast_task (which you probably don't want).

                  Try using a MERGE. But you'll need to fill in some code yourself since we don't have access to your tables and data. Here's a start (aka wild guess):
                  merge into forecast_task a
                  using (select b.project_task_id
                               ,b.budgeted_amount
                         from   project_task_budget b
                               ,forecast_info c
                         where  b.SOME_ID = c.SOME_ID      --< there must be some kind of join here
                         and    c.forecast_status_id = 9
                        ) u
                  on    (a.project_task_id = u.project_task_id) --< I don't know if more is needed here
                  when  matched then update
                        set a.amount = u.budgeted_amount;
                  1 2 Previous Next