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

Sam OZ
Sam OZ used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software Team Lead
Commented:
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')
Helena Markováprogrammer-analyst

Commented:
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;

Open in new window

Ensure you’re charging the right price for your IT

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

johnsoneSenior Oracle DBA
Commented:
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.
Helena Markováprogrammer-analyst

Commented:
@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...
johnsoneSenior Oracle DBA

Commented:
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.
awking00Information Technology Specialist

Commented:
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
johnsoneSenior Oracle DBA

Commented:
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.
awking00Information Technology Specialist

Commented:
@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) ;-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial