Re: Help needed for the 'subquery returns multiple rows' problem

Peter Lowagie wrote:
>
> Good day to the members of this newsgroup,
>
> For six months i have been working with Oracle7 and with some programs of
> the Developer 200 pack. And by working with it i learned more about PL/SQL
> and how to use it.
>
> Unfortunately, I'm still having problems with updating a column based on
> values from another table. Up till now i more or less bypassed the problem
> by making two cursors and updating one cursor with the values contained in
> the other table.
>
> This brings me to the following question: Is it possible to use a subquery
> in an update statement with contains different values (as the values i need
> are not the same for the entire column).
>
> Example: update COMPANY
> set company_name = (select a.comp_name from temp a, temp2 b
> where a.id = b.id);
>
> Gevolg: ORA- ..... : subquery returns multiple rows
>
> If it can't be done then my last question is:
> is it possible to fetch the first row AGAIN from a current cursor
> (as i need the same values to check another table and closing and opening
> the same cursor seems a
> big waste of time)
>
> All tips, trics or tactics will be highly appreciated,
>
> Peter Lowagie

The subquery will work fine as long as it returns a single value for each row just like the error essage indicates. So the task is to limit the subquery somehow if it could return mulitple rows. You could use min(comp_name), or max or some other function that returns one row, or elaborate the where clause to limit to one row.
Hope this helps. If it is still troublesome, post the details and you'll probably have several working options quickly.

Cheers,
Terry Received on Tue Jul 29 1997 - 00:00:00 CDT