Re: Can I limit the number or records to return given a condition?

antonyliu2..._at_yahoo.com wrote:
> antonyliu2002_at_yahoo.com wrote:
> > I need to update one field of T1 from T2. So, I tried this:
> >
> > update T1 set (MyField) = (select distinct FunField from T2 where
> > T1.ID = T2.ID);
> >
> > T1's ID is a proper subset of T2's ID. In other words, each ID of T1
> > has a corresponding record in T2.
> >
> > But, the problem is that, some IDs in T2 has multiple records, and as a
> > result, I get this error:
> >
> > ORA-01427: single-row subquery returns more than one row
> >
> > T1 has nearly 7500 records and T2 has nearly 200,000 records. I've
> > found some offending IDs from T2.
> >
> > I am not sure how to handle this problem. Can I somehow limit the
> > sub-query to return one and only one record given the condition?
> >
> > Thanks in advance.
>
> Thanks for those who are considering or have considered helping. This
> problem has been resolved by using rownum = 1 in the subquery to limit
> it to return 1 and only 1 record given the condition.

So, how do you guarantee that row 1 of your subquery result set has the particular distinct value of FunField that you want? If, for every value of T2.ID there are multiple possible values of FunField ...

Table T2:

ID          FunField
--           ------------
1           A
1           B
1           C

Do you want T1.MyField to be set to A, B, or C? "where rownum = 1" could return any one of them. Remember, 'rownum' is NOT the first row in the table (a meaningless concept anyway) but the first row in the returned result set. And without an ORDER BY, that could be any random row that meets the rest of the WHERE criteria. Received on Thu Sep 14 2006 - 16:02:01 CDT