Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Compare two tables and update one table based on the other table records

Posted on 2006-10-26
5
Medium Priority
?
1,315 Views
Last Modified: 2008-01-09
Hi,

I have 2 tables tabA and tabB
tabA has emp_id,hr_num,..... fields
tabB has e_emp_id,....... fields

I need to pull out the records from tabB where join condition would be emp_id = e_emp_id and now these records
need to be updated  with the values of hr_num in tabA.

I am looking for the cursor code.

Thanks
0
Comment
Question by:aaba646
4 Comments
 
LVL 11

Accepted Solution

by:
mohammadzahid earned 672 total points
ID: 17815548
I think it can be done using statement. Please take alook at the examples at url........

http://www.oracle.com/technology/products/oracle9i/daily/Aug24.html
0
 
LVL 6

Assisted Solution

by:Ritesh_Garg
Ritesh_Garg earned 664 total points
ID: 17816119

update tabB B
set       colB = (select hr_num from tabA where emp_id = B.e_emp_id)
where  exists  (select 1 from tabA where emp_id = B.e_emp_id)

Not sure why are you looking for cursor code but here its how it can be done:

Declare
   cursor  c_tabB is
   select   *
   from    tabB  B
   where exists  (select 1 from tabA where emp_id = B.e_emp_id)
   for update of col1;

   cursor  c_tabA (b_emp_id   NUMBER) is
   select   hr_num
   from    tabA   A
   where  emp_id = b_emp_id;

   t_hr_num    NUMBER;
Begin
   for r_tabB in c_tabB loop
       open c_tabA(r_tabB.e_emp_id);
       fetch c_tabA into t_hr_num;
       if   c_tabA%FOUND  then
           update  tabB
           set       colb = t_hr_num
           where current of c_tabB;
      end if;
      close  c_tabA;
   end loop;
end;
/

0
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 664 total points
ID: 17818429
I agree that a single statement can be used, not sure of the request for a cursor.

I believe the cursor can be simplified to:


begin

  for tabarec in (select emp_id, hr_num from taba) loop

    update tabb
        set hr_num = tabarec.hr_num
      where  e_emp_id = tabarec.emp_id;
 
  end loop;

  commit;

end;
0
 
LVL 1

Expert Comment

by:Computer101
ID: 20296185
Forced accept.

Computer101
EE Admin
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question