Re: Help HELP

From: fredericks <free101_at_picusnet.com>
Date: 1999/04/18
Message-ID: <37195855_at_news.itribe.net>#1/1


Jonus

Your example is not consistent in table names and aliases.

The error indicates that the sub-query returns multiple rows for each row that is being updated.
As only one number can be stored in total_available for any record, having multiple counts from the sub-query causes failure.

The most likely reason: there is no CORRELATION between the update record and the sub-query.

(BTW - You are updating ALL t records)

Rewriting your update - with some guesses/corrections about your table names and aliases - to include a correlation:

update tab t
set(total_available)=
 (select distinct count(s.L)

    from tuv s, xyz u
    where s.r = u.r (+)
    and s.fkey = t.prikey
    group by s.r);

in MY example , the sub-query where predicate:

    and s.fkey = t.prikey

correlates tab records with the sub-query, that is - if I guess your semantics -
the total is computed pertinent to each tab record.

Without the correlation - all tab records will receive the same total_available value.

Another approach would be to write a server function to do the calculation.

The update would then look like this:

Update tab
set total_available = get_distinct_L_form_tuv(tab.prikey);

For some tricks with functions:

See Functional Magic paper by John C. Lennon at:

http://members.aol.com/jomarlen2/oraclesh.html

Also I do not see the use of the outer join to xyz in your example. It doesn't restrict the result set from tuv and it does not appear to be used in the select list, in the group by or in any where predicate that filters the sub-query results. (unless L is from xyz).

HTH Mark

Jonus wrote in message <370CE274.E9807C6B_at_yahoo.com>...
>when executing a query, I recieve the error message below.
>
>update t set(total_available)=
>(select distinct
> count(L)
>from tuv t, xyz x
>where s.r = c.r (+)
>group by s.r);
>
>ERROR at line 2:
>ORA-01427: single-row subquery returns more than one row
>
>Anyone know how i should write this to get a result of total_available??
>
>Book Title Cost total_available
>123 The Firm 23 5
>
>with many thanks
>Jonus
Received on Sun Apr 18 1999 - 00:00:00 CEST

Original text of this message