Re: UPDATE with correlatted subquery

"Jaap W. van Dijk" <j.w.vandijk_at_hetnet.nl> wrote in message news:3c5d2b97.2236577_at_news.jaapwvandijk.myweb.nl...
> That was what I was referring to: if the subquery can't find a record
> it still will come up with a result of one 'record' with a value. Have
> you tried my solution and seen it to give a compilation or runtime
> error?

Yes, I tried it. It still gives:

ORA-01407: cannot update ("AGGREGATOR"."POINTS_RANKING"."POINTS") to NULL

.. because the when there is no corresponding row in the correlated subquery, it was trying to do the following:

UPDATE points_ranking SET points = NULL ... blah...

.. meaning it came up with no record at all for the corresponding row in the table being updated. In other words, the SELECT was never evaluated at all, so that even putting NVL() in there is no help.

>
> Also, Ganesh Raja, in een follow up to your posting, gives a solution
> that also uses the NVL function and has checked that it worked.

Tried that. Works in SQLPlus, but gives compliation error in PL/SQL. Been hit by that one before.

Thanks anyway. I think the only solution is a TEMPORARY TABLE.

>
> Jaap.
>
> On Sun, 03 Feb 2002 19:01:54 +1100, "Jesus M. Salvo Jr."
> <jmsalvo_at_ihug.com.au> wrote:
>
> >Nope. Wont work. Syntactically its ok.
> >
> >But the subquery if the subquery returns null, "SELECT
> >points_ranking.poitns + NVL( SUM( b.points, 0 )) " will still return
null,
> >because the row being updated does not have a corresponding row in the
> >subquery.
> >
> >The solution seems to be to find the proper outer join correlated
subquery
> >... but have tried various combinations.
> >
> >Jonathan posted a solution, but I wanted one that does the subquery only
> >once.
> >
> >
> >
> >Jaap W. van Dijk wrote:
> >
> >> 'points_ranking.points + SUM( b.points )'
> >>
> >> can be NULL because if the subquery does not select any records
> >> SUM(b.points) will be NULL, and adding something to NULL gives NULL.
> >> This can be remedied by using the NVL-function:
> >>
> >> 'points_ranking.points + NVL(SUM( b.points ),0)'
> >>
> >> I'm not able to check, but I'm pretty sure Oracle will accept this
> >> syntactically.
> >>
> >> Jaap.
> >>
> >>
> >> On Fri, 01 Feb 2002 08:04:24 GMT, "Jesus M. Salvo Jr."
> >> <john_at_softgame.com.au> wrote:
> >>
> >>>I want to update multiple rows but one column of a table based on a
> >>>subquery. The idea is to increase ( or decrease ) the points of a
player
> >>>based on additional points that he/she may have earned after a certain
> >>>period:
> >>>
> >>>UPDATE points_ranking
> >>>SET points =
> >>> (
> >>> SELECT points_ranking.points + SUM( b.points )
> >>> FROM sgtest.game a, sgtest.transaction b, sgtest.player c
> >>> WHERE
> >>> a.game_type_id = 10 AND
> >>> c.service_provider_id = 3 AND
> >>> b.game_id = a.game_id AND
> >>> b.player_id = c.player_id AND
> >>> c.player_id (+) = points_ranking.player_id AND
> >>> a.start_date > fromdate
> >>> GROUP BY 3, 10, c.player_id
> >>> )
> >>>WHERE
> >>> service_provider_id = 3 AND
> >>> game_type_id = 10;
> >>>
> >>>
> >>>... where fromdate is a variable.
> >>>
> >>>
> >>>However, the points column is NOT NULL, and it is possible that the
> >>>correlational subquery may not return any rows for the player where it
is
> >>>correlated to ... resulting in a:
> >>>
> >>>ORA-01407: cannot update ("AGGREGATOR"."POINTS_RANKING"."POINTS") to
NULL
> >>>
> >>>Does anybody have an idea on how to perform an UPDATE from a
correlational
> >>>subquery that ensures that each row being UPDATEd has a row from the
> >>>correlated subquery?
> >>>
> >>>I have tried placing an outer join either way ... still the same.
> >>>
> >>>
> >>>John
> >>>
> >>>
> >>>
> >
>
Received on Sun Feb 03 2002 - 19:56:37 CST