question about updating a column

From: titsataki <titsataki1995-google_at_yahoo.com>
Date: 11 Dec 2003 16:54:57 -0800
Message-ID: <69e7d2bd.0312111654.6777ed9@posting.google.com>

hi everybody,

I trying to update a table named account. the column that I want to update is called BPSTATE. BPSTATE is a VARCHAR:  

BPSTATE VARCHAR2(4000) It seems that it sorts in aphabetical order.

select bpstate from account

    where substr(bpstate,1,5)='"4.1"'
    and rownum < 10;

the results are:

BPSTATE

---

"4.1" /models/Account(Active,Correlated,Approved)\\
"4.1" /models/Account(Correlated,Approved,Inactive)\\
"4.1" /models/Account(Active,Correlated,UnknownApproval)\\
"4.1" /models/Account(Correlated,Inactive,UnknownApproval)\\
"4.1" /models/Account(Active,Correlated,UnknownApproval)\\
"4.1" /models/Account(Correlated,Inactive,UnknownApproval)\\
"4.1" /models/Account(Active,Correlated,UnknownApproval)\\
"4.1" /models/Account(Active,Correlated,UnknownApproval)\\
"4.1" /models/Account(Active,Correlated,UnknownApproval)\\

I thought I can do

update account
set SUBSTR(BPSTATE,30,10)= 'Uncorrelated' where in
(select bpstate from account

    where
  substr(30,10) = 'Correlated'
  and VTOID like '1234567890';)

I am a little beacuse of the sorting as I want to replace the Correlated with Uncorrelated and not just add it.

Is there a generic SQL command that does not need the substring, but it finds a replaces a value with another value regadrless where it is? (replace Correlated with Uncorrelated). And still does not mess with the the rest of the info?

Thanks

Nick Received on Thu Dec 11 2003 - 18:54:57 CST