update using an alternative to correlated subquery

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.otherColumn = :other_value )

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

update ML_StagePosition sp
set sp.stageProcessFlag = 1
where exists (

     select 1 
     from CS_Position p
	where p.NAME = sp.managerName 
	and p.EffectiveStartDate <= sp.EffectiveStartDate 
	and p.EffectiveEndDate  >= sp.EffectiveEndDate 
	and p.RemoveDate >= sp.EffectiveEndDate
	and p.genericNumber1 <= sp.AgentLevel
	and p.ruleElementOwnerSeq = (Select min(p2.ruleElementOwnerSeq)
			               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.stageProcessFlag stageFlag

     from ML_StagePosition sp, CS_Position p     where p.ruleElementOwnerSeq = (select min(p2.RULEELEMENTOWNERSEQ)

		                      from CS_Position p2
			         where p2.name = sp.managername
				  and p2.EffectiveStartDate <= sp.EffectiveStartDate
			          and p2.EffectiveEndDate  >= sp.EffectiveEndDate
				  and p2.RemoveDate >= sp.EffectiveEndDate 
				  and p2.genericNumber1 <= sp.AgentLevel))

Thanks for your help.

-Murali Received on Fri Oct 03 2003 - 00:07:24 CEST