473,324 Members | 2,313 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

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 14975

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Jerry Brenner | last post by:
Our users have potentially dirty legacy data that they need to get into our application. We provide a set of staging tables, which map to our source tables, that the users do their ETL into. ...
1
by: Chris Michael | last post by:
I've only just started using update queries and have a problem with the following query in that it comes back with this error: "Subquery returned more than 1 value. This is not permitted when the...
6
by: Karen Middleton | last post by:
In MS Access I can do in one SQL statement a update if exists else a insert. Assuming my source staging table is called - SOURCE and my target table is called - DEST and both of them have the...
10
by: sqlgoogle | last post by:
Hi I'm trying to update a db based on the select statement which has ORDER BY in it. And due to that I'm getting error which states that Server: Msg 1033, Level 15, State 1, Line 13 The ORDER...
17
by: kalamos | last post by:
This statement fails update ded_temp a set a.balance = (select sum(b.ln_amt) from ded_temp b where a.cust_no = b.cust_no and a.ded_type_cd = b.ded_type_cd and a.chk_no = b.chk_no group by...
3
by: Colin Spalding | last post by:
In Access, if I want to update one table with information from another, all I need to do is to create an Update query with the two tables, link the primary keys and reference the source...
8
by: Venkata C | last post by:
Hi! Does anyone here know of a way to goad DB2 into converting a correlated subquery to a non-correlated one? Does DB2 ever do such a conversion? We have a query of the form SELECT .. FROM A...
10
by: Steve Jorgensen | last post by:
Hi all, Over the years, I have had to keep dealing with the same Access restriction - that you can't update a table in a statement that joins it to another non-updateable query or employs a...
0
by: Murali | last post by:
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...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.