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?
LVL 6
anumosesAsked:
Who is Participating?
 
Alexander Eßer [Alex140181]Connect With a Mentor Software DeveloperCommented:
This one's from http://stackoverflow.com, the second update should run faster when updating more than just 1 column:

This is called a correlated update

UPDATE table1 t1
   SET (name, desc) = (SELECT t2.name, t2.desc
                         FROM table2 t2
                        WHERE t1.id = t2.id)
 WHERE EXISTS (
    SELECT 1
      FROM table2 t2
     WHERE t1.id = t2.id )

Assuming the join results in a key-preserved view, you could also

UPDATE (SELECT t1.id,
               t1.name name1,
               t1.desc desc1,
               t2.name name2,
               t2.desc desc2
          FROM table1 t1,
               table2 t2
         WHERE t1.id = t2.id)
   SET name1 = name2,
       desc1 = desc2

0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
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...)
0
 
anumosesAuthor Commented:
I have altered my table with column name area_rep_no number(4). This is how its in blood_drives table
0
 
anumosesAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.