oracle subquery with multiple output

Question

Here is my table [answer] and [student_answer]

create table answer(
varchar2(10) ques_id,
varchar2(10) ans,
primary key (ques_id)
);

create table student_answer(
varchar2(10) stud_id,
varchar2(10) quiz_id,
varchar2(10) ques_id,
varchar2(10) ans,
number(3) mark,
primary key (stud_id,quiz_id,ques_id)
);

UPDATE student_answer
SET mark = 1
WHERE * IN (select ques_id, stud_id, quiz_id from answer m
right outer join student_answer sa
on (sa.ques_id = m.ques_id and sa.ans=m.ans)
where m.ques_id is not null
order by sa.ques_id);

*i got confused here

how can i get multiple row output per subquery? so that i can update a table according to the result?

* every student have different id
* every quiz have different id
* every question have different id

Filipe Silva · Accepted Answer · 2013-12-08 15:00:06Z

1

To use IN you have to have only one column at the left and the possibilities for that column at the right.

But i think you can probably do your UPDATE just with an EXISTS. Try this:

UPDATE student_answer sa
SET mark = 1
WHERE EXISTS (
    SELECT 1
    FROM answer m
    WHERE sa.ques_id = m.ques_id
      AND sa.ans = m.ans
    );

answered Dec 8, 2013 at 15:00

Filipe Silva

21.7k6 gold badges56 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

oracle subquery with multiple output

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related