Path: news.easynews.com!easynews!news-out.cwix.com!newsfeed.cwix.com!newsfeed.nyc.globix.net!news.stealth.net!news2.euro.net!blue.nl.gxn.net!cyan.nl.gxn.net!not-for-mail
From: j.w.vandijk@hetnet.nl (Jaap W. van Dijk)
Newsgroups: comp.databases.oracle.server
Subject: Re: UPDATE with correlatted subquery
Date: Sat, 02 Feb 2002 17:10:42 GMT
Organization: XO Communications B.V.
Lines: 62
Message-ID: <3c5c1bef.4679732@news.jaapwvandijk.myweb.nl>
References: <c_r68.28065$Ni2.154878@news-server.bigpond.net.au>
NNTP-Posting-Host: asd-tel-ap02-d01-016.dial.freesurf.nl
X-Trace: cyan.nl.gxn.net 1012669527 5551 62.100.14.16 (2 Feb 2002 17:05:27 GMT)
X-Complaints-To: abuse@freesurf.nl
NNTP-Posting-Date: 2 Feb 2002 17:05:27 GMT
X-Newsreader: Forte Free Agent 1.21/32.243
Xref: easynews comp.databases.oracle.server:133683
X-Received-Date: Sat, 02 Feb 2002 10:04:16 MST (news.easynews.com)

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