By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,130 Members | 2,228 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,130 IT Pros & Developers. It's quick & easy.

update using an alternative to correlated subquery

P: n/a
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 =

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 = 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
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.