how to update multiple queries and commit using oracle

Hi,

I have multiple update queries in oracle like this

update dm_location_s set path='somepath' where object_id='001';

Open in new window


update dm_location_s set path='somepath' where object_id='002';

Open in new window



Currently i'm executing every query seperately and commiting them. Is there any way i can execute all those updates queries in one shot and then commit them.

sukumarrAsked:
Who is Participating?
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.

CarlsbergFTWCommented:
yes you can commit at the end of all transactions, there's no need to commit after every update statement.

0
CarlsbergFTWCommented:
or try using a case statement for each object id in case the patch needs to be different. Should ease your work.
0
sukumarrAuthor Commented:
Can i use like this

update dm_location_s set path='somepath' where object_id='002', set path='someotherpath' where object_id='003'

Open in new window

commit

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

johanntagleCommented:
use case as CarlsbergFTW mentioned

update dm_location_s set path =
  case
    when object_id='002' then 'somepath'
    when object_id='002' then 'someotherpath'
    default 'defaultpath'
   end;
0
CarlsbergFTWCommented:
update dm_location_s  set path  = case when object_id='002'  then 'somepatch002' when object_id = 'other value' then 'other path' else 'some default path'

0
CarlsbergFTWCommented:
and then commit of course so changes save after session ends
0
johanntagleCommented:
Sorry type the the other line should have been  when object_id='003' then 'someotherpath'.

Also, do check what happens if you don't use default, I'm not sure maybe to be safe you can have "default path", which would be like "set path=path" meaning retain current value.
0
johanntagleCommented:
oh yeah i'm getting mixed up the keyword for the default value is "else" not "default"
0
sukumarrAuthor Commented:
hmm i'm executing below query but it gives missing keyword error

update dm_location_s set  file_system_path = case when r_object_id='3a00f22780000500' then 'E:\Documentum\data\Petrofac\content_storage_01\Documentum\data\Petrofac\content_storage_01'
when r_object_id='3a00f22780000504' then 'D:\Documentum\dba\log'
default ''
end;

Open in new window



oracle-error.jpg
0
CarlsbergFTWCommented:
update dm_location_s set  file_system_path = case when r_object_id='3a00f22780000500' then 'E:\Documentum\data\Petrofac\content_storage_01\Documentum\data\Petrofac\content_storage_01'
when r_object_id='3a00f22780000504' then 'D:\Documentum\dba\log'
end;
0
devindCommented:
replace default with else and then try

UPDATE dm_location_s
   SET file_system_path      =
          CASE
             WHEN r_object_id = '3a00f22780000500'
             THEN
                'E:\Documentum\data\Petrofac\content_storage_01\Documentum\data\Petrofac\content_storage_01'
             WHEN r_object_id = '3a00f22780000504'
             THEN
                'D:\Documentum\dba\log'
             ELSE
                ''
          END;
UPDATE dm_location_s
   SET file_system_path      =
          CASE
             WHEN r_object_id = '3a00f22780000500'
             THEN
                'E:\Documentum\data\Petrofac\content_storage_01\Documentum\data\Petrofac\content_storage_01'
             WHEN r_object_id = '3a00f22780000504'
             THEN
                'D:\Documentum\dba\log'
             ELSE
                ''
          END;

Open in new window

0
CarlsbergFTWCommented:
are you sure you want to update whole table with ' ' as path ? just trying to figure out how many rows are you trying to affect with your update and not do a stupid thing,  the good part is that you need to commit after updating so make 100% sure that you updated the correct columns with the correct value(s) else "rollback;" and start over.
And make sure you try giving as many details as possible, even if you don't know what you're doing someone here will!
0
sukumarrAuthor Commented:
@CarlsbergFTW
I want to change only the specified rows and  i don't want to update whole table with ''. Thanks for pointing it.

Your query gives error as well.saying invalid characters

update dm_location_s set  file_system_path = case when r_object_id='3a00f22780000500' then 'E:\Documentum\data\Petrofac\content_storage_01\Documentum\data\Petrofac\content_storage_01'
when r_object_id='3a00f22780000504' then 'D:\Documentum\dba\log'
end;
0
sukumarrAuthor Commented:
@devind i didn't check your query. But does it update all other values in tables to ' '. Actually i want to change only the mentioned values with out touching other rows.
0
CarlsbergFTWCommented:
Okay here it goes

i tried updating one of my oracle db as follows:

UPDATE employees
   SET first_name =
          CASE
             WHEN employee_id = '100'
             THEN
                'firstname1'
             WHEN employee_id = '101'
             THEN
                'firstname2' else first_name end;
         ;

Open in new window

your query should go like this:

UPDATE dm_location_s
   SET file_system_path =
          CASE
             WHEN r_object_id = '3a00f22780000500'
             THEN
                'E:\Documentum\data\Petrofac\content_storage_01\Documentum\data\Petrofac\content_storage_01'
             WHEN r_object_id = '3a00f22780000504'
             THEN
                'D:\Documentum\dba\log' else r_object_id
          END;

Open in new window


this will only update the lines where 3a00f22780000504 and 3a00f22780000500 is present and will leave other intact.

Please update.
0
CarlsbergFTWCommented:
update
0
sukumarrAuthor Commented:
hmm i had executed that and it's updating almost 32 rows. So i rolled backed them :(
0
CarlsbergFTWCommented:
try this please:
select count (r_object_id) from dm_location_s
 where r_object_id = '3a00f22780000500' and r_object_id = '3a00f22780000504'

Open in new window

0
sukumarrAuthor Commented:
I really appreciate your help.

Your query returns 0. Because r_object_id is unique value for each row.

I executed the below query and it gave 2 results


select count (r_object_id) from dm_location_s
 where r_object_id in('3a00f22780000500','3a00f22780000504')
0
CarlsbergFTWCommented:
the the code i gave you should be updating 2 rows!

i just figured there was a litle error in my update statement.


Try this! should update 2 rows.
UPDATE dm_location_s
   SET file_system_path =
          CASE
             WHEN r_object_id = '3a00f22780000500'
             THEN
                'E:\Documentum\data\Petrofac\content_storage_01\Documentum\data\Petrofac\content_storage_01'
             WHEN r_object_id = '3a00f22780000504'
             THEN
                'D:\Documentum\dba\log' else file_system_path
          END;
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
sukumarrAuthor Commented:
Sorry!! It's still updating all the 32 rows..
0
CarlsbergFTWCommented:
yes it does update all 32 rows but their values remain unchanged. Because if the update statement does not find 3a00f22780000500 or 3a00f22780000504 it will input the current values. select the data before passing the update and after so you can compare.
0
CarlsbergFTWCommented:
you can see that at the end of the update: "... else file_system_path" so the value inserted will be the already existing one, thus data is not really updated but gets passed and processed
0
sukumarrAuthor Commented:
Yes. Perfect solution and it worked for me....Thanks a lot for help
0
Amitkumar PSr. ConsultantCommented:
Try the following

update 
    dm_location_s 
set  
    file_system_path = decode(r_object_id, 
        '3a00f22780000500', 'E:\Documentum\data\Petrofac\content_storage_01\Documentum\data\Petrofac\content_storage_01', 
        '3a00f22780000504', 'D:\Documentum\dba\log', 
        file_system_path);

commit;

Open in new window

0
CarlsbergFTWCommented:
With pleasure
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.

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.