Re: question about updating a column

titsataki1995-google_at_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