Re: question about updating a column

titsataki wrote:

> 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

No it doesn't! Rows 2 and 3 are not sorted!

-- 
Regards, Frank van Bortel