script to update row with condition

Posted on 2006-03-20
Last Modified: 2008-01-09

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,

Question by:Snody
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    update yourtable
    set yourcolumn = concat ( youcolumn , '"' )
    where substr ( yourcolumn , length( yourcolumn) 1 ) <> '"'
    LVL 12

    Accepted Solution

    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 ) <> '"'
    LVL 34

    Assisted Solution

    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.

    Author Comment

    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

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
    Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
    Via a live example, show how to take different types of Oracle backups using RMAN.
    This video shows how to recover a database from a user managed backup

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now