This discussion is archived
1 2 Previous Next 20 Replies Latest reply: Jan 31, 2013 2:39 PM by SomeoneElse Go to original post RSS
  • 15. Re: update recors
    sb92075 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Yes.. I am trying to update the amount .

    Please help me out and correct the query.
  • 17. Re: update recors
    969952 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points