OraFAQ Forum: Server Administration » how to update part of the column data

Home » RDBMS Server » Server Administration » how to update part of the column data

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to update part of the column data [message #53868]

Tue, 15 October 2002 10:34

Eugene
Messages: 44
Registered: August 2001

Member

Hi all,
Could someone help me to update part of the column.
If I do:
Update table_1
Set substr(col_1,10,4) = 'Blah';
I get error: missing equal sign

Is there a way to do it? Is there a built-in function in Oracle to perform it?
Thanks,

Report message to a moderator

Re: how to update part of the column data [message #53872 is a reply to message #53868]

Tue, 15 October 2002 10:48

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

this is not a RECOMENDED METHOD...but works..
SQL GURUS....may help more

SQL> select ename,job from emp;

ENAME      JOB
---------- ---------
SMITH      CLERK
ALLEN      SALESMAN
WARD       SALESMAN
JONES      MANAGER
MARTIN     SALESMAN
BLAKE      MANAGER
CLARK      MANAGER
KING       PRESIDENT
TURNER     SALESMAN
JAMES      CLERK
FORD       ANALYST
MILLER     CLERK
mag        CLERK
vivek      CLERK

14 rows selected.
SQL> ED
Wrote file afiedt.buf

  1* update emp set job=REPLACE(JOB,'SALES','fire')
SQL> /

14 rows updated.

SQL> select ename,job from emp;

ENAME      JOB
---------- ---------
SMITH      CLERK
ALLEN      fireMAN
WARD       fireMAN
JONES      MANAGER
MARTIN     fireMAN
BLAKE      MANAGER
CLARK      MANAGER
KING       PRESIDENT
TURNER     fireMAN
JAMES      CLERK
FORD       ANALYST
MILLER     CLERK
mag        CLERK
vivek      CLERK

14 rows selected.

Report message to a moderator

Re: how to update part of the column data [message #53874 is a reply to message #53872]

Tue, 15 October 2002 11:04

Eugene
Messages: 44
Registered: August 2001

Member

Thank you for your reply.
Why is this method not recommended?
What is the difference between doing that as oppose to doing something like that:
update table_1
set col_1 = substr(col_1,9)||'Blah'||substr(col_14)

Regards,

Report message to a moderator

Re: how to update part of the column data [message #53876 is a reply to message #53872]

Tue, 15 October 2002 11:29

andrew again
Messages: 2577
Registered: March 2000

Senior Member

Mahesh's solution is fine, if you aren't worried about the position of the change. If you only want to update a specific region of the field, then you need to use substr() to restrict the region. You can mix and match approaches. As long as your algorithm is robust and handles exception cases correclty (null fields, fields shorter or longer etc) and there is no major performance difference then the solution is fine.

Report message to a moderator

Previous Topic:	dba
Next Topic:	End-of-communication ....

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Sep 19 17:59:05 CDT 2024