Update statement in Oracle with joins The Next CEO of Stack OverflowHow to select the nth row in a SQL database table?Get list of all tables in Oracle?When do I need to use a semicolon vs a slash in Oracle SQL?Oracle Multiple Overlapping Between StatementsUpdate statement from SQL Server to OracleUpdate statement with inner join on OracleIs there a combination of “LIKE” and “IN” in SQL?SQL statement joining Oracle and MS SQL ServerOracle - update join - non key-preserved tableSQL ORACLE UPDATE with Join
Contours of a clandestine nature
Skipping indices in a product
Rotate a column
What flight has the highest ratio of time difference to flight time?
Anatomically Correct Strange Women In Ponds Distributing Swords
How to avoid supervisors with prejudiced views?
Written every which way
Do I need to enable Dev Hub in my PROD Org?
What is the result of assigning to std::vector<T>::begin()?
Is "for causing autism in X" grammatical?
Would a completely good Muggle be able to use a wand?
Are there any limitations on attacking while grappling?
What benefits would be gained by using human laborers instead of drones in deep sea mining?
Is it ever safe to open a suspicious html file (e.g. email attachment)?
How to solve a differential equation with a term to a power?
How powerful is the invisibility granted by the Gloom Stalker ranger's Umbral Sight feature?
Multiple labels for a single equation
Bold, vivid family
What can we do to stop prior company from asking us questions?
If the heap is initialized for security, then why is the stack uninitialized?
What happens if you roll doubles 3 times then land on "Go to jail?"
Novel about a guy who is possessed by the divine essence and the world ends?
Is it possible to search for a directory/file combination?
How to Reset Passwords on Multiple Websites Easily?
Update statement in Oracle with joins
The Next CEO of Stack OverflowHow to select the nth row in a SQL database table?Get list of all tables in Oracle?When do I need to use a semicolon vs a slash in Oracle SQL?Oracle Multiple Overlapping Between StatementsUpdate statement from SQL Server to OracleUpdate statement with inner join on OracleIs there a combination of “LIKE” and “IN” in SQL?SQL statement joining Oracle and MS SQL ServerOracle - update join - non key-preserved tableSQL ORACLE UPDATE with Join
I have the below code in SQL Server:
UPDATE RH SET
--SELECT RH.economic_class ,
economic_class = C.economic_class
FROM dbo.tblRecHist RH
JOIN tblComp C ON RH.company_id = C.company_id
WHERE RH.company_id = @CompID
AND RH.rec_date = @RecDate
Which I have converted in Oracle to:
UPDATE tblRecHist
SET economic_class = (Select C.economic_class from tblComp C join tblRecHist
RH on RH.company_id = C.company_id)
WHERE RH.company_id = v_CompID
AND RH.rec_date = v_RecDate;
As this is a part of a big procedure I am unable to execute this independently.
So, my question is are both the logic correct? If not, what could be the possible change?
oracle plsql
edited Mar 7 at 22:54
William Robertson
8,50732233
asked Mar 7 at 15:26
vikkyvikky
378
add a comment |
I have the below code in SQL Server:
UPDATE RH SET
--SELECT RH.economic_class ,
economic_class = C.economic_class
FROM dbo.tblRecHist RH
JOIN tblComp C ON RH.company_id = C.company_id
WHERE RH.company_id = @CompID
AND RH.rec_date = @RecDate
Which I have converted in Oracle to:
UPDATE tblRecHist
SET economic_class = (Select C.economic_class from tblComp C join tblRecHist
RH on RH.company_id = C.company_id)
WHERE RH.company_id = v_CompID
AND RH.rec_date = v_RecDate;
As this is a part of a big procedure I am unable to execute this independently.
So, my question is are both the logic correct? If not, what could be the possible change?
oracle plsql
edited Mar 7 at 22:54
William Robertson
8,50732233
asked Mar 7 at 15:26
vikkyvikky
378
add a comment |
I have the below code in SQL Server:
UPDATE RH SET
--SELECT RH.economic_class ,
economic_class = C.economic_class
FROM dbo.tblRecHist RH
JOIN tblComp C ON RH.company_id = C.company_id
WHERE RH.company_id = @CompID
AND RH.rec_date = @RecDate
Which I have converted in Oracle to:
UPDATE tblRecHist
SET economic_class = (Select C.economic_class from tblComp C join tblRecHist
RH on RH.company_id = C.company_id)
WHERE RH.company_id = v_CompID
AND RH.rec_date = v_RecDate;
As this is a part of a big procedure I am unable to execute this independently.
So, my question is are both the logic correct? If not, what could be the possible change?
oracle plsql
edited Mar 7 at 22:54
William Robertson
8,50732233
asked Mar 7 at 15:26
vikkyvikky
378
I have the below code in SQL Server:
UPDATE RH SET
--SELECT RH.economic_class ,
economic_class = C.economic_class
FROM dbo.tblRecHist RH
JOIN tblComp C ON RH.company_id = C.company_id
WHERE RH.company_id = @CompID
AND RH.rec_date = @RecDate
Which I have converted in Oracle to:
UPDATE tblRecHist
SET economic_class = (Select C.economic_class from tblComp C join tblRecHist
RH on RH.company_id = C.company_id)
WHERE RH.company_id = v_CompID
AND RH.rec_date = v_RecDate;
As this is a part of a big procedure I am unable to execute this independently.
So, my question is are both the logic correct? If not, what could be the possible change?
oracle plsql
oracle plsql
edited Mar 7 at 22:54
William Robertson
8,50732233
asked Mar 7 at 15:26
vikkyvikky
378
edited Mar 7 at 22:54
William Robertson
8,50732233
asked Mar 7 at 15:26
vikkyvikky
378
edited Mar 7 at 22:54
William Robertson
8,50732233
edited Mar 7 at 22:54
William Robertson
8,50732233
edited Mar 7 at 22:54
William Robertson
8,50732233
8,50732233
asked Mar 7 at 15:26
vikkyvikky
378
asked Mar 7 at 15:26
vikkyvikky
378
asked Mar 7 at 15:26
vikkyvikky
378
378
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
That logic won't work becuase your subquery will return more than one record. Instead of joining tblRecHist into your subquery, omit it and correlate the subquery with the current row of tblRecHist from the UPDATE.
UPDATE tblRecHist rh
SET economic_class = (Select C.economic_class
from tblComp C
WHERE C.company_id = RH.company_id )
WHERE RH.company_id = v_CompID
AND RH.rec_date = v_RecDate;
This assumes company_id is a unique key in tblComp.
answered Mar 7 at 15:30
Matthew McPeakMatthew McPeak
10.3k11234
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55047333%2fupdate-statement-in-oracle-with-joins%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
That logic won't work becuase your subquery will return more than one record. Instead of joining tblRecHist into your subquery, omit it and correlate the subquery with the current row of tblRecHist from the UPDATE.
UPDATE tblRecHist rh
SET economic_class = (Select C.economic_class
from tblComp C
WHERE C.company_id = RH.company_id )
WHERE RH.company_id = v_CompID
AND RH.rec_date = v_RecDate;
This assumes company_id is a unique key in tblComp.
answered Mar 7 at 15:30
Matthew McPeakMatthew McPeak
10.3k11234
add a comment |
That logic won't work becuase your subquery will return more than one record. Instead of joining tblRecHist into your subquery, omit it and correlate the subquery with the current row of tblRecHist from the UPDATE.
UPDATE tblRecHist rh
SET economic_class = (Select C.economic_class
from tblComp C
WHERE C.company_id = RH.company_id )
WHERE RH.company_id = v_CompID
AND RH.rec_date = v_RecDate;
This assumes company_id is a unique key in tblComp.
answered Mar 7 at 15:30
Matthew McPeakMatthew McPeak
10.3k11234
add a comment |
That logic won't work becuase your subquery will return more than one record. Instead of joining tblRecHist into your subquery, omit it and correlate the subquery with the current row of tblRecHist from the UPDATE.
UPDATE tblRecHist rh
SET economic_class = (Select C.economic_class
from tblComp C
WHERE C.company_id = RH.company_id )
WHERE RH.company_id = v_CompID
AND RH.rec_date = v_RecDate;
This assumes company_id is a unique key in tblComp.
answered Mar 7 at 15:30
Matthew McPeakMatthew McPeak
10.3k11234
That logic won't work becuase your subquery will return more than one record. Instead of joining tblRecHist into your subquery, omit it and correlate the subquery with the current row of tblRecHist from the UPDATE.
UPDATE tblRecHist rh
SET economic_class = (Select C.economic_class
from tblComp C
WHERE C.company_id = RH.company_id )
WHERE RH.company_id = v_CompID
AND RH.rec_date = v_RecDate;
This assumes company_id is a unique key in tblComp.
answered Mar 7 at 15:30
Matthew McPeakMatthew McPeak
10.3k11234
answered Mar 7 at 15:30
Matthew McPeakMatthew McPeak
10.3k11234
answered Mar 7 at 15:30
Matthew McPeakMatthew McPeak
10.3k11234
answered Mar 7 at 15:30
Matthew McPeakMatthew McPeak
10.3k11234
10.3k11234
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55047333%2fupdate-statement-in-oracle-with-joins%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
