Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> UPDATE with correlatted subquery

UPDATE with correlatted subquery

From: Jesus M. Salvo Jr. <john_at_softgame.com.au>
Date: Fri, 01 Feb 2002 08:04:24 GMT
Message-ID: <c_r68.28065$Ni2.154878@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;

... 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 Fri Feb 01 2002 - 02:04:24 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US