Re: UPDATE with correlatted subquery

From: jonathan.bliss <jonathanbliss_at_cs.com>
Date: Fri, 1 Feb 2002 15:25:17 -0000
Message-ID: <a3ec09$1t$1@suaar1ac.prod.compuserve.com>

Not really a server question but try ...

 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
    EXISTS (
    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

    )
 AND service_provider_id = 3 AND

    game_type_id = 10;

Jonathan

"If I don't embarass myself with the easy ones I'll try answering the harder questions"

"Jesus M. Salvo Jr." wrote in message
news:c_r68.28065$Ni2.154878_at_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 - 09:25:17 CST