Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!prodigy.com!in.100proofnews.com!in.100proofnews.com!news-out.visi.com!hermes.visi.com!newsfeed2.dallas1.level3.net!news.level3.com!postnews1.google.com!not-for-mail
From: vslabs@onwe.co.za (Billy Verreynne)
Newsgroups: comp.databases.oracle.misc
Subject: Re: question about updating a column
Date: 12 Dec 2003 00:43:11 -0800
Organization: http://groups.google.com
Lines: 42
Message-ID: <1a75df45.0312120043.6910aba2@posting.google.com>
References: <69e7d2bd.0312111654.6777ed9@posting.google.com>
NNTP-Posting-Host: 198.54.206.91
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1071218591 18245 127.0.0.1 (12 Dec 2003 08:43:11 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 12 Dec 2003 08:43:11 +0000 (UTC)
Xref: newssvr20.news.prodigy.com comp.databases.oracle.misc:133956

titsataki1995-google@yahoo.com (titsataki) wrote 

> 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)

Varchar2 is varchar2. Size does not matter wrt what you are trying to
do.

> 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';)

As you find out, this is not legal. SUBSTR is a function. What you
tried is akin to trying to set the value of x by doing :   MAX(x) :=
10.

You can set x := 10. You can set x := MAX(x) (not that this makes much
sense ;-). However, you cannot assign a value to a function (except
when dealing with a function-looka-like property of a class that has
both a getter and a setter).

> 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?

Look at the REPLACE function. Something like:
UPDATE foo
  SET col1 = REPLACE(col1, 'jck;, 'jack' )
WHERE whatever


--
Billy