We help IT Professionals succeed at work.

script to update row with condition

Snody
Snody asked
on
Medium Priority
1,556 Views
Last Modified: 2008-01-09
Hi,

I need a sciprt for oracle to update a column. The single entry only should updated if a condition is met.

A little bit more detailed:
If the last char is a ", the script should do nothing. If not " is the last char, it should add a " at the end of the text string. Then commit the changes and go to the next entry...

It would be nice if anybody can help me :)

Greets and best regards,
Andy


Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
update yourtable
set yourcolumn = concat ( youcolumn , '"' )
where substr ( yourcolumn , length( yourcolumn) 1 ) <> '"'
Commented:
There should be a comma between length and 1 and you may want to add is not null:

update yourtable
set yourcolumn = concat ( yourcolumn , '"' )
where yourcolumn IS NOT NULL
  AND substr ( yourcolumn , length( yourcolumn) ,1 ) <> '"'

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
johnsoneSenior Oracle DBA
CERTIFIED EXPERT
Commented:
If the requirement is actually that each row must be committed individually, then a single statement will not suffice.  It would have to be PL/SQL, something like this:

begin
  for c1rec in (select rowid from <tab> where <col> is not null and substr(<col>, length(<col>)) <> '"' ) loop
    update <tab>
      set <col> = <col> ||  '"'
      where rowid = c1rec.rowid;
    commit;
  end loop;
end;
/


Depending on the size of the table, you may run into ORA-01555.  If you do, you will have to code around it by closing and reopening the cursor every few thousand records.

Author

Commented:
Thx @ both of you. I used the solution of geotiger but johnsone gave me an exact answer for my question. But i din't have to commit every row. I did it only once. But I'm sure johnsone's way would work too...

Greets and best Regards,
Andy
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.