Solved

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

Posted on 2006-10-26
5
1,300 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
5 Comments
 
LVL 11

Accepted Solution

by:
mohammadzahid earned 168 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 166 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 34

Assisted Solution

by:johnsone
johnsone earned 166 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to recover a database from a user managed backup

808 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