[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1321
  • Last Modified:

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

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
aaba646
Asked:
aaba646
3 Solutions
 
mohammadzahidCommented:
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
 
Ritesh_GargCommented:

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
 
johnsoneSenior Oracle DBACommented:
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
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now