Re: UPDATE with correlatted subquery

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?

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.

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 - 06:31:35 CST