469,903 Members | 1,369 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,903 developers. It's quick & easy.

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

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

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

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)

(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))
set stageFlag = 1
Thanks for your help.

Jul 19 '05 #1
0 14814

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Chris Michael | last post: by
10 posts views Thread by sqlgoogle | last post: by
17 posts views Thread by kalamos | last post: by
3 posts views Thread by Colin Spalding | last post: by
10 posts views Thread by Steve Jorgensen | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.