We help IT Professionals succeed at work.

Check out this week's podcast, "Dairy Farms to Databases: Community's Hand in Technology"Listen Now

x

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

Sam OZ
Sam OZ asked
on
146 Views
Last Modified: 2018-04-19
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

CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Helena Markováprogrammer-analyst
CERTIFIED EXPERT

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') )
Alex [***Alex140181***]Software Developer
CERTIFIED EXPERT

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

johnsoneSenior Oracle DBA
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Helena Markováprogrammer-analyst
CERTIFIED EXPERT

Commented:
@johnson - you are right :)
Alex [***Alex140181***]Software Developer
CERTIFIED EXPERT

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

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
CERTIFIED EXPERT

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
CERTIFIED EXPERT

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
CERTIFIED EXPERT

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.
Alex [***Alex140181***]Software Developer
CERTIFIED EXPERT

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.