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
Sam OZAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
try:

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Helena Markováprogrammer-analystCommented:
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') )
0
Alex [***Alex140181***]Software DeveloperCommented:
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

1
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

johnsoneSenior Oracle DBACommented:
This is the way that I would have always done it.
UPDATE tbla 
SET    fld1 = (SELECT fld1 
               FROM   tblb 
               WHERE  a.fld2 = b.fld2) 
WHERE  fld2 IN (SELECT fld2 
                FROM   tblb 
                WHERE  fld3 NOT IN ( '000', '001' )); 

Open in new window

While similar to what Helena posted, that post would not work (it should error), because the subquery is missing in the SET.
1
Helena Markováprogrammer-analystCommented:
@johnson - you are right :)
0
Alex [***Alex140181***]Software DeveloperCommented:
@johnson: You're hitting the table tblb twice! No need to do this ;-) That's sort of bad practice to me...
0
johnsoneSenior Oracle DBACommented:
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.
0
awking00Information Technology SpecialistCommented:
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
0
johnsoneSenior Oracle DBACommented:
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.
0
awking00Information Technology SpecialistCommented:
@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.
0
Alex [***Alex140181***]Software DeveloperCommented:
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) ;-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.