Oracle

problem with update

Please support our Oracle advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Oct 2007

Posts: 280

Reputation: joshmo is an unknown quantity at this point

Solved Threads: 19

joshmo

Offline

Posting Whiz in Training

problem with update

Nov 16th, 2008

I have written my simple query using update and in a file with a .sql extension. However, I cant seem to update my table and yet at the end of the input it shows me 1 row updated I would also want to know how I can do 2 updates simultaneously. Thanks. Here is the code

 Help with Code Tags 
 Oracle Syntax (Toggle Plain Text) 
accept ID prompt 'Enter Old ID:'
accept ID prompt 'Enter New ID:'
accept Name prompt 'Name:'
 
UPDATE  table_one
SET ID=REPLACE(ID,'&ID','&ID')
WHERE Name='&Name';
accept ID prompt 'Enter Old ID:'
accept ID prompt 'Enter New ID:'
accept Name prompt 'Name:'

UPDATE  table_one
set ID=replace(ID,'&ID','&ID')
where Name='&Name';

Last edited by joshmo; Nov 16th, 2008 at 8:45 am.

•

Join Date: Dec 2006

Posts: 44

Reputation: alit2002 is an unknown quantity at this point

Solved Threads: 1

alit2002

Offline

Light Poster

Re: problem with update

Dec 24th, 2008

Hi

Your script should update the record assuming the ID and name exist. Try a commit after the update to save the change.

UPDATE table_one
set ID=replace(ID,'&ID','&ID')
where Name='&Name';
commit;
select * from table_one; -- will show your change

Don't perform the update in one sqlplus window and try and view the results in another as the results won't show until you've issued a commit statement.

Hope it helps.

Merry Christmas
Alistair

•

Join Date: Oct 2008

Posts: 17

Reputation: vimotaru is an unknown quantity at this point

Solved Threads: 1

vimotaru

Offline

Newbie Poster

Re: problem with update

Mar 6th, 2009

To do 2 updates or more you only have to add your lines to the script. But be sure to make a commit at the bottom as alit2002.

You can add as many querys as you need.

If you have just inserted data, then you may need to put a commit after every query.

Hope it helps

•

Join Date: Feb 2007

Posts: 2,073

Reputation: debasisdas will become famous soon enough

Solved Threads: 124

debasisdas debasisdas is offline

Offline

Postaholic

Re: problem with update

Mar 9th, 2009

once you COMMIT that will be reflected in the database table.

if you exit without commit all the uncommitted transactions will be rolled back.

Share your Knowledge.

•

Join Date: Oct 2008

Posts: 17

Reputation: vimotaru is an unknown quantity at this point

Solved Threads: 1

vimotaru

Offline

Newbie Poster

Re: problem with update

Mar 10th, 2009

I was just reading your code and I have a question:

Are the variables names ok?
Maybe you need to name the 'ID' vars different. IDo and IDn for example.

Maybe the problem with the update is that the 'replace' cannot do his job.

If that is an errata...well... try with SO parameters.
Hope it works.