1 2 Previous Next 20 Replies Latest reply: Jan 31, 2013 4:39 PM by SomeoneElse RSS

    no tkt

    969952
      N/A

      Edited by: 966949 on Jan 31, 2013 9:45 AM
        • 1. Re: update recors
          Frank Kulash
          966949 wrote:
          Hi All,

          Please find the below requirement and provide me the logic.
          I don't see any requirements below; all I see is some code, which I assume does not meet your requirements. How can anyone possibly know what your requirements are, given only one example of what they are not?

          Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved.
          Also post the results you want from that data, and an explanation of how you get those results from that data, with specific examples.
          If you're asking about a DML statement, such as UPDATE, the CREATE TABLE and INSERT statements should re-create the tables as they are before the DML, and the results will be the contents of the changed table(s) when everything is finished.
          Always say which version of Oracle you're using (for example, 11.2.0.2.0).
          See the forum FAQ {message:id=9360002}
          Update forecast_task.amount data from project_task_budget . budgeted_amount if forecast_id associated record in table forecast_info.forecast_status_id=9
          There is no FROM clause in an UPDATE statement.
          An UPDATE statement may have a sub-query, in which case the sub-query will have a FROM clause. An example of the syntax is
          UPDATE  table_1  m
          SET     column_1 =
                  (
                      SELECT  column_2
                      FROM    table_2
                      WHERE   ...
                  )
          ;
          table_1 and table_2 may be the same table.
          The sub-query may reference columns from table_1, using the table alias m; this is called a Correlated Sub-Query .
          For more details and examples, see the SQL Language manual.
          • 2. Re: update recors
            969952
            N?A

            Edited by: 966949 on Jan 31, 2013 11:27 AM
            • 3. Re: update recors
              sb92075
              966949 wrote:
              Hi,

              The below one is the requirement.

              Update forecast_task.amount data from project_task_budget . budgeted_amount if forecast_id associated record in table forecast_info.forecast_status_id=9
              which row in PROJECT_TASK_BUDGET supplies the correct value used to update row in FORECAST_TASK?
              • 4. Re: update recors
                969952
                i.e Project_task_id
                • 5. Re: update recors
                  969952
                  N/A

                  Edited by: 966949 on Jan 31, 2013 11:27 AM
                  • 6. Re: update recors
                    sb92075
                    966949 wrote:
                    I have written the query as follows but getting error please have a look.
                    UPDATE  forecast_task a
                    SET    a. amount =
                    (
                    SELECT  b.budgeted_amount 
                    FROM    project_task _budget b)
                    WHERE   a.project_task_id = b.project_task_id
                    ) and select a.forecast_id  in(select forecast_status_id from forecast_info where forecast_status_id=9);
                    Thanks.
                    Since you did not provide us CREATE TABLE statement, I can't run post code above.
                    Since you did not provide complete COPY & PASTE from above I have no idea what error you got.
                    Since I can't tell what you did wrong, I can't guess what needs to change to eliminate the error.



                    How do I ask a question on the forums?
                    SQL and PL/SQL FAQ
                    • 7. Re: update recors
                      969952
                      Please find the below error message.
                      SQL Error: ORA-00911: invalid character
                      00911. 00000 -  "invalid character"
                      *Cause:    identifiers may not start with any ASCII character other than
                                 letters and numbers.  $#_ are also allowed after the first
                                 character.  Identifiers enclosed by doublequotes may contain
                                 any character other than a doublequote.  Alternative quotes
                                 (q'#...#') cannot use spaces, tabs, or carriage returns as
                                 delimiters.  For all other contexts, consult the SQL Language
                                 Reference Manual.
                      *Action:
                      • 8. Re: update recors
                        sb92075
                        SET    a. amount =
                                (
                                    SELECT  b.budgeted_amount 
                                    FROM    project_task _budget b)
                                    WHERE   a.project_task_id = b.project_task_id
                                ) and select a.forecast_id  in(select forecast_status_id from forecast_info where forecast_status_id=9);  2    3    4    5    6    7  
                                    FROM    project_task _budget b)
                                                         *
                        ERROR at line 5:
                        ORA-00911: invalid character
                        • 9. Re: update recors
                          969952
                          I have written the query as follows but getting error please have a look.
                          UPDATE  forecast_task a
                          SET    a. amount =
                                  (
                                      SELECT  b.budgeted_amount 
                                      FROM    project_task _budget b)
                                      WHERE   a.project_task_id = b.project_task_id   ( after this it should satisfy the below condition as well the value which is retrieving from 3rd table)
                                  ) ---- it  need to satisfy the condition  (           select forecast_status_id from forecast_info where forecast_status_id=9 );
                          Thanks.
                          • 10. Re: update recors
                            sb92075
                            966949 wrote:
                            I have written the query as follows but getting error please have a look.
                            UPDATE  forecast_task a
                            SET    a. amount =
                            (
                            SELECT  b.budgeted_amount 
                            FROM    project_task _budget b)
                            There is a space character between "PROJECT_TASK" & "_BUDGET" in the line above that throws the error!
                            • 11. Re: update recors
                              969952
                              after deleting the spaces also getting the same error and please find the requirement from 3rd table as well.
                              • 12. Re: update recors
                                sb92075
                                How do I ask a question on the forums?
                                SQL and PL/SQL FAQ
                                • 13. Re: update recors
                                  969952
                                  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.
                                  • 14. Re: update recors
                                    SomeoneElse
                                    I don't see a join predicate on these two tables in the subquery:

                                    from project_task_budget b,
                                    forecast_info c
                                    1 2 Previous Next