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

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') )

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;

Select allOpen in new window

@johnson - you are right :)

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

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.

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

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.

@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.

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) ;-)