Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!in.100proofnews.com!in.100proofnews.com!news2.telebyte.nl!newshub1.home.nl!home.nl!not-for-mail
From: Frank <fbortel@nescape.net>
Newsgroups: comp.databases.oracle.misc
Subject: Re: question about updating a column
Date: Fri, 12 Dec 2003 09:21:20 +0100
Organization: @Home Benelux
Lines: 56
Message-ID: <brbt9b$bvj$1@news3.tilbu1.nb.home.nl>
References: <69e7d2bd.0312111654.6777ed9@posting.google.com>
Reply-To: fbortel@nescape.net
NNTP-Posting-Host: cc28855-a.hnglo1.ov.home.nl
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Trace: news3.tilbu1.nb.home.nl 1071216747 12275 217.121.193.81 (12 Dec 2003 08:12:27 GMT)
X-Complaints-To: abuse@home.nl
NNTP-Posting-Date: Fri, 12 Dec 2003 08:12:27 +0000 (UTC)
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.4) Gecko/20030624 Netscape/7.1 (ax)
X-Accept-Language: en-us, en
In-Reply-To: <69e7d2bd.0312111654.6777ed9@posting.google.com>
Xref: newssvr20.news.prodigy.com comp.databases.oracle.misc:133954

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