oracle11g - Oracle 11g: Need to get the longest matching string that starts with another string -

i have 2 tables data:

create table tbl1 (   id number,   label varchar2(50) ); create table tbl2 (   id number,   src varchar2(400) );  insert tbl1 values (1, 'foobar'); insert tbl1 values (2, 'foo'); insert tbl1 values (3, 'bar');  insert tbl2 (src) values ('foo: yeah'); insert tbl2 (src) values ('foobar: nope'); 

i trying update id field of tbl2 match longest matching string tbl1. intention 'foo: yeah' entry should id 2, , 'foobar: nope' entry should id of 1:

update tbl2 t2 set t2.id = (select t1.id              tbl1 t1              t2.src t1.label || '%'); 

doing results in error: "single-row subquery returns more 1 row". makes sense me, tried this:

update tbl2 t2 set t2.id = (select t1.id              tbl1 t1              t2.src t1.label || '%'              , rownum=1              order length(t1.label) desc); 

but error: "missing right parenthesis". don't understand error in context, parenthesis formed.

the "missing right parenthesis" because of order by clause in subquery; isn't valid there.

you're getting rownum in same level you're ordering. won't quite expect. ordering happens last, you'll 1 row - row, 1 indeterminate - , you'll order single row length, doesn't anything.

you need level of subquery row you're interested in:

update tbl2 t2 set t2.id = (   select id (     select t1.id     tbl1 t1     t2.src t1.label || '%'     order length(t1.label) desc   )   rownum=1 ); 

... unfortunately doesn't work, , error: ora-00904: "t2"."src": invalid identifier.

that's because can't refer table or alias 2 levels of subquery down, @ least until 12c (possibly; there recent question seemed suggest works now, , this older one).

you can use the first function , keep dense_rank achieve this:

update tbl2 t2 set t2.id = (   select min(t1.id) keep (dense_rank first order length(t1.label) desc)   tbl1 t1   t2.src t1.label || '%' );  2 rows updated.  select * tbl2;          id src                 ---------- --------------------          2 foo: yeah                     1 foobar: nope         

you use keep (dense_rank last order length(t1.label)), i.e. last instead of first, , without desc, logically same if don't have nulls.