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

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? Received on Thu Sep 14 2006 - 17:53:57 CDT