oracle - REGEXP_SUBSTR to update a table -
i trying use regexp_substr
update column in table , getting error, missing expression.
update p_table set f_name = select regexp_substr ( (select user_name user_info rowid = r_id ('user_info')), '[^ ]{1,15}', 1, 1) dual;
when add parentheses before first select , after dual works sets rows in column same value. not need them unique cannot have them same. advice give me.
update p_table set f_name = (select regexp_substr ( (select user_name user_info rowid = r_id ('user_info')), '[^ ]{1,15}', 1, 1) dual);
as mentioned in @patrickmarchand comment oracle optimizes query, function called once. asktom article makes clear.
to workaround must introduce linked p_table rows function parameters force oracle call every row. example rowid
p_table
:
update p_table set f_name = ( select regexp_substr( ( select user_name user_info rowid = r_id ('user_info', p_table.rowid) ), '[^ ]{1,15}', 1, 1 ) dual )
Comments
Post a Comment