469,087 Members | 1,267 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,087 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
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
0 14785

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 kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.