Re: UPDATE with correlatted subquery

"Jesus M. Salvo Jr." <john_at_softgame.com.au> wrote in message news:<pTl78.34139$Ni2.193853_at_news-server.bigpond.net.au>...
> "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.

Use Execute Immediate ... Imeediate Solution for all Incompatability Problems between the SQL and Pl/SQL...

>
> 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
> > >>>
> > >>>
> > >>>
> > >
> >

Regards,
Ganesh R Received on Mon Feb 04 2002 - 01:08:33 CST