0

I have the below queries and would like to update the corresponding data in the table that the queries return. Is it possible to utilize an update statement with an Oracle function? If so, how?

select *
from A
where translate(transnbr, '_0123456789', '_') is not null ;

select *
from A
where regexp_like(transnbr, '/*-+.') ;

Both queries return records as expected.

Would something along the lines the below work?

update a
set transnbr = translate(transnbr, '_0123456789', '_') ;

update a
set transnbr = regexp_like(transnbr, '/*-+.') ;

Thanks for any replies

user2607902
  • 13
  • 5
  • Without reviewing the actual usage, the initial answer is that TRANSLATE can be used in that way, but REGEXP_LIKE cannot. Any function that returns a SQL datatype should be usable in the SET operation. However, REGEXP_LIKE does not return a SQL datatype; it returns a boolean. – Chris Hep Nov 16 '16 at 18:13
  • Can't you try your queries? – Aleksej Nov 16 '16 at 18:41
  • No. Don't have execute permissions. – user2607902 Nov 16 '16 at 19:02
  • Try something like `select transnbr, translate(transnbr, '_0123456789', '_') from a` and `select transnbr, regexp_like(transnbr, '/*-+.') from a`; if this works and the results are correct, then your update will work fine. – Aleksej Nov 16 '16 at 19:05
  • @Aleksej - thanks, I'll give it a try – user2607902 Nov 16 '16 at 19:45
  • You can create index table with your new columns then you can Update – CompEng Nov 17 '16 at 05:40

0 Answers0