Re: UPDATE with correlatted subquery

'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 Sat Feb 02 2002 - 11:10:42 CST