update using an alternative to correlated subquery

[Murali]

Hi All

I was reading thro the posting(s) of Thomas Kyte and his nifty
approach to doing updates without the need for unnecessary correlated
subqueries. An alternative to correlated subquery using this technique
is:

update
( select columnName, value
from name, lookup
where name.keyname = lookup.keyname
and lookup.otherCol umn = :other_value )
set columnName = value

Here is a correlated subquery that works for an update I am trying to
do:

update ML_StagePositio n sp
set sp.stageProcess Flag = 1
where exists (
select 1
from CS_Position p
where p.NAME = sp.managerName
and p.EffectiveStar tDate <= sp.EffectiveSta rtDate
and p.EffectiveEndD ate >= sp.EffectiveEnd Date
and p.RemoveDate >= sp.EffectiveEnd Date
and p.genericNumber 1 <= sp.AgentLevel
and p.ruleElementOw nerSeq = (Select min(p2.ruleElem entOwnerSeq)
from CS_position p2
where p.name = p2.name))



Now here is how i tried to achieve this same query using his technique
(and i got the ORA 01779 error)

update
(Select sp.stageProcess Flag stageFlag
from ML_StagePositio n sp, CS_Position p
where p.ruleElementOw nerSeq = (select min(p2.RULEELEM ENTOWNERSEQ)
from CS_Position p2
where p2.name = sp.managername
and p2.EffectiveSta rtDate <= sp.EffectiveSta rtDate
and p2.EffectiveEnd Date >= sp.EffectiveEnd Date
and p2.RemoveDate >= sp.EffectiveEnd Date
and p2.genericNumbe r1 <= sp.AgentLevel))
set stageFlag = 1


Thanks for your help.

-Murali