This discussion is archived
8 Replies Latest reply: Jan 21, 2013 9:23 AM by 973995 RSS

corelated update

973995 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    Tell me why my car won't go
    Because you're sitting at your desk?
  • 3. Re: corelated update
    sb92075 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    Can you show the output of 'describe answers'?
  • 5. Re: corelated update
    padders Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    >
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    SomeoneElse thanks for your help.

Legend

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