Solved

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

Posted on 2006-10-26
5
1,298 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle and DateTime math 6 37
Oracle SQL - Query help 7 54
create a nested synonym 4 25
Oracle - Query link database loop 8 38
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

776 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