Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to update a field by appending data to it

794093Mar 24 2011 — edited Mar 24 2011
Hello all.

I need to be able to "repair" some table data (mytable) as follows. One of the columns is a VARCHAR2(255) called "plan", and it can have several strings in it, separated by semicolons, e.g. "THING1:3;CONTRACT:2".

The problem I have is that some of the rows are missing the CONTRACT part altogether, e.g.

"THING1:3"

I would like to be able to write a SQL query that will update these to include some
type of default value for the CONTRACT part, so it looks like the first example above.

Can I do this outside of PL/SQL? Essentially what I need to do for the UPDATE is to append "CONTRACT:3" to whatever is the existing value.

Does this make sense?

At first I thought this might be a job for regular expressions, but since I will already
know which rows need this, I don't have to match anything to be used later.

Here's my thought of SQL pseudocode:

UPDATE mytable
set plan = plan + ";CONTRACT:3"
where (my criteria)

Thanks,

Mitch

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 21 2011
Added on Mar 24 2011
4 comments
30,252 views