Sub query returning multi values in oracle

Question

I have some null values in my table, I have to update that values from other table. I am using the following query but the sub query returns more than one value.

update student st 
set n_id=(select n_id 
          from class cl 
          where st.uid=cl.uid 
          and ( cl.start_date='31-jan-2011' or cl.start_date='28-feb-2011') 
          and st.n_id is null);

How can I update my field. Help me to find out. Thanks.

you need to ask yourself, why does the sub query return more than 1 value? if you are trying to set the row to a specific value, you obviously need just a single ( and correct ) value returned, so you need to look at what is currently returned and fix up your where clause to limit it to what you actually want. — Matthew Watson
– Matthew Watson, Commented Nov 2, 2011 at 3:16

Vitor Freitas · Accepted Answer · 2011-11-02 03:06:42Z

2

you could either work on the logic or add the statement

and rownum = 1

then your subquery will always return just one row (the first one.. if it exists)

answered Nov 2, 2011 at 3:06

Vitor Freitas

3,6201 gold badge27 silver badges35 bronze badges

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

1 Comment

Dan Over a year ago

This is correct. However, @Samurai before using this approach think carefully and make sure that of all the values returned by that subquery, you really don't care which one gets used. If you do care then you need to figure out which is the "correct" value and change your subquery to return only that one.

Collectives™ on Stack Overflow

Sub query returning multi values in oracle

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related