Re: UPDATE with correlatted subquery

From: Jesus M. Salvo Jr. <john_at_softgame.com.au>
Date: Mon, 04 Feb 2002 01:47:11 GMT
Message-ID: <zKl78.34121$Ni2.193837@news-server.bigpond.net.au>

Valid it is in SQLPlus ... but not in PL/SQL. Once again, I have been hit with this difference between parser engines in PL/SQL and the database.

Thanks anyway.

"Ganesh Raja" <ganesh_at_gtfs-gulf.com> wrote in message news:a8aed4.0202030207.2af08fc1_at_posting.google.com... > "Jesus M. Salvo Jr." <john_at_softgame.com.au> wrote in message news:<c_r68.28065$Ni2.154878_at_news-server.bigpond.net.au>...
> > 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;

>
>  UPDATE points_ranking
>  SET points = points_ranking.points +
>     Nvl( (
>     SELECT 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
>     ),0)
>  WHERE
>     service_provider_id = 3 AND
>     game_type_id = 10;
>
> This Works In 8.1.7
>

> >
> >
> > ... 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 Sun Feb 03 2002 - 19:47:11 CST