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
is:

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

-Murali
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.