Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> UPDATE with correlatted subquery
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
... 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