Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: update email address
Sheela wrote:
> Hi there,
> I would like to update 5000 email address from
>
> name_at_xxxxxx.com to name_at_yyyyy.com.
> name1_at_xxxxxx.com to name1_at_yyyyy.com
> -----
> ------
> -----
> I tried this statment :
> update student a set
> substr(a.email_addr,(instr(a.email_addr,'@')+1),10) = 'yyyyy.com'where
> stud_id like 'name'
>
>
> but I am getting ORA-00927: missing equal sign
>
Malcolm Dew-Jones wrote:
> HansF (News.Hans_at_telus.net) wrote: > : On Tue, 05 Apr 2005 13:55:02 -0700, Sheela interested us by writing: > > : > update student a set > : > substr(a.email_addr,(instr(a.email_addr,'@')+1),10) = 'yyyyy.com'where > > : Basically the SET clause requires a column on the left side immediately > : after the 'set', but you seem to have a wild expression in place of the > : column name. (Nor does your statement meet any of the other variations > : permitted.) > > It looks like valid perl to update a portion of a string. I suspect the > poster has "losing track of multiple contexts" syndrome. >
Of course in Perl you would be more likely to do a simple search and replace, but I assume you mean, Perl's use of a function such as substr() as an lvalue (something you can assign to).
For the OP: if using version 10g, you might try this: (no error checking for malformed e-mail addresses in this example):
update student
set EMAIL_ADDR = regexp_substr(EMAIL_ADDR, '.*@') || 'yyyyy.com';
BTW, I found another documentation bug: ORA-00927 is not included in version 10g on-line docs.
http://otn.oracle.com/pls/db10g/db10g.error_search?search=00927
-Mark Bole Received on Tue Apr 05 2005 - 17:56:06 CDT