asked on

update oracle table

I am adding a new column called area_rep_id to the table ad_request. I have to update all the records with area_rep_no fronm blood_drives table where site_code from ad_request = site code from blood drives and similarly the drive date.

I have a query here.

update ad_request
set area_rep_id = (
select bd.area_rep_no
from ad_request ar, blood_drives bd
where ar.drive_date = bd.drive_date
and ar.site_code = bd.site_code)

Is this correct?

Alex [***Alex140181***]

Have you already altered your ad_request table?!
If not, you might want to consider this one (essentially when dealing with many rows):

create table ad_request_new nologging as
  select ar.... -- here goes your "old" column list
         bd.area_rep_no as area_rep_id -- you may put the new column wherever you want it
    from blood_drives bd,
         ad_request ar 
   where bd.drive_date(+) = ar.drive_date
     and bd.site_code(+) = ar.site_code;

drop table ad_request;

rename ad_request_new to ad_request;

alter table ad_request looging;

Open in new window

Don't forget about recreation of indexes and triggers and recompiling invalid objects (like packages, functions...)

ASKER CERTIFIED SOLUTION

Alex [***Alex140181***]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

anumoses

ASKER

I have altered my table with column name area_rep_no number(4). This is how its in blood_drives table

anumoses

ASKER

thanks