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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Alexander Eßer [Alex140181]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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.