Re: UPDATE with correlatted subquery

From: Jesus M. Salvo Jr. <jmsalvo_at_ihug.com.au>
Date: Sun, 03 Feb 2002 19:01:54 +1100
Message-ID: <1012722959.287914@bugstomper.ihug.com.au>

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 - 02:01:54 CST