Solved

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

Posted on 2006-10-26
5
1,302 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 35

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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.

729 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