Avatar of Sam OZ
Sam OZ
Flag for Australia asked on

update Oracle 11g table tblA based on a condiion on tblA and tbl2

I need to update Oracle 11g  table   tblA based on a condiion on tblA and tbl2

I tried
update tblA    set Fld1 = B.Fld1   from tblA A inner   join tblB B     on A.Fld2 = B.Fld2    where B.Fld3 not in ( '000' , '001')

But query throws error . Can you please give me the query
Oracle Database

Avatar of undefined
Last Comment
Alex [***Alex140181***]

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Ryan Chong

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Helena Marková

Here is another option:
update A set A.Fld1 = B.Fld1 where  A.Fld2 in (Select B.Fld2 from B where B.Fld3 not in ( '000' , '001') )
Alex [***Alex140181***]

Or use MERGE:
merge into A dest
using (select Fld1
         from B
        where B.Fld3 not in ('000', '001')) src
on (dest.Fld2 = src.Fld2)
when matched then
  update
     set dest.Fld1 = src.Fld1;

Open in new window

SOLUTION
johnsone

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Helena Marková

@johnson - you are right :)
Your help has saved me hundreds of hours of internet surfing.
fblack61
Alex [***Alex140181***]

@johnson: You're hitting the table tblb twice! No need to do this ;-) That's sort of bad practice to me...
johnsone

Bad practice or not, it was the only way to do it.  The MERGE statement and using joins in an UPDATE are relatively new.

I made no claim that it was the best way to do it.  And not sure why I got that comment and Helena didn't.  Assuming an indexed lookup in the SET, which it should be, then minimal impact on the second access.  Depending on indexing, the MERGE could be worse (or error), but without plans it is difficult to tell.
awking00

johnsone's method is not the only way to do it. Using the merge is a perfectly good means of updating the table as well. It may or may not perform better than using the update with subqueries. I would suggest running an explain plan on both methods to see. Also, the merge statement as presented needs some modification -
merge into tblA A
using
(select fld1, fld2 from tblB where fld3 not in ('000','001')) B
on (A.fld2 = B.fld2)
when matched then
update set
A.fld1 = B.fld1
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
johnsone

I never claimed that it is the only way to do it.  I claimed that it was the only way to do it and that there are new ways that allow other syntax.

MERGE and UPDATE with joins have restrictions.  If your statement doesn't get you past all the restrictions then those methods don't work.  The UPDATE with the sub-queries, will work in almost all cases.
awking00

@johnsone, I apologize for my misinterpretation of your statement. I agree that before the advent of the merge function, it was pretty much the only way to do it. I still think it would be prudent to use both methods to 1) see that they both provide the same results, and 2) run the explain plan on each to see if there is better performance of one method over the other.
Alex [***Alex140181***]

Depending on indexing, the MERGE could be worse (or error), but without plans it is difficult to tell.
I really doubt that, but anyways...
I totally agree with you, that it was not possible back then, yes. And yes, one has to get used to using MERGE for these kind of scenarios (I was very sceptic too in the beginning, trust me)...
To be honest, in the end it's up to you and/or the one in charge to do such SQL statements and you should go for that one where you feel most comfortable with ;-)

P.S.: @johnsone: I really didn't mean to be rude/offenisive or bother you (since you're one of "my heroes" here on EE) ;-)
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes