Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1541
  • Last Modified:

script to update row with condition


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,

2 Solutions
Guy Hengel [angelIII / a3]Billing EngineerCommented:
update yourtable
set yourcolumn = concat ( youcolumn , '"' )
where substr ( yourcolumn , length( yourcolumn) 1 ) <> '"'
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 ) <> '"'
johnsoneSenior Oracle DBACommented:
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:

  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;
  end loop;

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.
SnodyAuthor 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,

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now