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

From: EdStevens <quetico_man_at_yahoo.com>
Date: 15 Sep 2006 05:45:38 -0700
Message-ID: <1158324338.409189.245470@b28g2000cwb.googlegroups.com>

antonyliu2002_at_yahoo.com wrote:
> EdStevens wrote:
> > 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.

>

> Hi, Ed, that is a very good question, which I am aware of. And with
> this approach, I am selecting the first record that is returned,
> whatever it is.

>

> I am actually trying to populate a student table with students' email
> userids from our Enterprise Data Warehouse (EDW). Unfortunately, I
> just checked, a single studentID may have multiple records in the EDW
> like so:

>


> STU_ID    EMAIL_ID   EMAIL_DOMAIN   EFF_DT

> 1        johndoe     myuniv.edu     SEP-14-06

> 1        johndoe1   myuniv.edu    SEP-14-06

> 1        jdoe       otheruniv.edu    SEP-01-03

>

> I want their email_id for myuniv.edu, but the student has 2 such ids,
> so, I had to select only 1 of them, not sure which one he will be
> using. But what else can I do, given this situation, right?

so, if WHERE EMAIL_DOMAIN='myuniv.edu' is part of your selection, then I guess the business case here says it really doesn't matter whethere you get 'johndoe' or 'johndoe1'. That was not at all clear in your original, as you tried to 'dummy up' the example. Perhaps you could add ORDER BY EFF_DT DESC so that your rownum 1 is the most recent email_id for the selected student. Received on Fri Sep 15 2006 - 07:45:38 CDT