How to update a field by appending data to it
794093Mar 24 2011 — edited Mar 24 2011Hello 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