8 Replies Latest reply: Jan 21, 2013 11:23 AM by 973995 RSS

    corelated update

    973995
      My first question is

      can I update inline view like below?
      update 
      
      (select * from surveys s join answers a on s.survey_id = a.survey_seq_id where month = 201212 and qa = 1 and main_group_id = 55) a, 
      (select * from surveys s join answers a on s.survey_id = a.survey_seq_id where month = 201212 and qa = 1 and main_group_id = 3) b
      where a.survey_id = b.survey_id and substr(a.question_uid , 3) = substr(b.question_uid, 2))
      
      set b.answer = a.answer;
      My second question is

      The following query is giving error. What can I do?
      update answers ans set (ans.answer) = 
      (with
      a as (select * from surveys s join answers a on s.survey_id = a.survey_seq_id where month = 201212 and qa = 1 and main_group_id = 55),
      b as (select * from surveys s join answers a on s.survey_id = a.survey_seq_id where month = 201212 and qa = 1 and main_group_id = 3)
      select a.answer
      from a join b on a.survey_id = b.survey_id and substr(a.question_uid , 3) = substr(b.question_uid, 2) and b.answer_id = ans.answer_id) 
      where ans.main_group_id = 3;
      
      SQL Error: ORA-00904: "ANS"."ANSWER_ID":
      Thanks

      Edited by: 970992 on 21.Oca.2013 08:03

      Edited by: 970992 on 21.Oca.2013 08:08
        • 1. Re: corelated update
          sb92075
          970992 wrote:
          The following query is giving error. What can I do?
          update answers ans set (ans.answer) = 
          (with
          a as (select * from surveys s join answers a on s.survey_id = a.survey_seq_id where month = 201212 and qa = 1 and main_group_id = 55),
          b as (select * from surveys s join answers a on s.survey_id = a.survey_seq_id where month = 201212 and qa = 1 and main_group_id = 3)
          select a.answer
          from a join b on a.survey_id = b.survey_id and substr(a.question_uid , 3) = substr(b.question_uid, 2) and b.answer_id = ans.answer_id) 
          where ans.main_group_id = 3;
          Thanks
          How do I ask a question on the forums?
          SQL and PL/SQL FAQ


          see a picture of my car.
          Tell me why my car won't go.

          ERROR?
          What error?
          I don't see any error!
          • 2. Re: corelated update
            padders
            Tell me why my car won't go
            Because you're sitting at your desk?
            • 3. Re: corelated update
              sb92075
              padders wrote:
              Tell me why my car won't go
              Because you're sitting at your desk?
              I am in the back seat of my car access the 'Net via WiFi!
              • 4. Re: corelated update
                Syed Ullah
                Can you show the output of 'describe answers'?
                • 5. Re: corelated update
                  padders
                  I am in the back seat of my car
                  I'm pretty sure you have to sit in the front to drive unless you're Hightower from Police Academy.
                  • 6. Re: corelated update
                    973995
                    >
                    Can you show the output of 'describe answers'?
                    >

                    describe answers
                    
                    Name          Null     Type          
                    ------------- -------- ------------- 
                    ANSWER_ID     NOT NULL NUMBER(10)    
                    SURVEY_SEQ_ID          NUMBER(10)    
                    QUESTION_ID            NUMBER(5)     
                    SUB_GROUP_ID           NUMBER(4)     
                    MAIN_GROUP_ID          NUMBER(4)     
                    QUESTION_UID           NUMBER(15)    
                    ANSWER                 VARCHAR2(300) 
                    ANSWER2                VARCHAR2(300) 
                    ANSWER3                VARCHAR2(20)  
                    ANSWER4                VARCHAR2(20)  
                    • 7. Re: corelated update
                      SomeoneElse
                      If I move the AND clause with the error to the WHERE clause I don't get an error:
                      SQL> update answers ans
                        2  set (ans.answer) = (with
                        3                        a as (select * from surveys s join answers a on s.survey_id = a.survey_seq_id where month = 201212 and qa = 1 and main_group_id = 55),
                        4                        b as (select * from surveys s join answers a on s.survey_id = a.survey_seq_id where month = 201212 and qa = 1 and main_group_id = 3)
                        5                      select a.answer
                        6                      from   a
                        7                      join   b on a.survey_id = b.survey_id
                        8                               and substr(a.question_uid , 3) = substr(b.question_uid, 2)
                        9                      where  b.answer_id = ans.answer_id        ----< moved from the ON clause
                       10                     )
                       11  where ans.main_group_id = 3;
                      
                      0 rows updated.
                      You'll have to test it yourself to see if it's logically correct.
                      • 8. Re: corelated update
                        973995
                        SomeoneElse thanks for your help.