Re: Update on table depending on data in same table

>
> Sorry, I just saw in your original posting that you *can* identify the
> problem ones. You could try something like the following...
>
> SQL> desc t0208
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
>
> C NUMBER
> D VARCHAR2(10)
> E VARCHAR2(100)
>
> SQL> select * from t0208;
>
> C D E
> ---------- ----------
> --------------------------------------------------
> 1 steve engllish;spanish;german;
> 2 jim english;german;
> 11 steve italian;
>
> SQL> declare
> 2 l_name varchar2(20);
> 3 l_langs varchar2(200);
> 4 l_this_lang varchar2(200);
> 5 cursor cur is
> 6 select *
> 7 from t0208
> 8 where c <= 10 for update;
> 9 cursor cur1 is
> 10 select *
> 11 from t0208
> 12 where d = l_name
> 13 and c > 10 for update;
> 14 begin
> 15 for c in cur loop
> 16 l_name := c.d;
> 17 for c1 in cur1 loop
> 18 l_langs := c1.e;
> 19 while (instr(l_langs,';') > 0) loop
> 20 l_this_lang := substr(l_langs,1,instr(l_langs,';') -1);
> 21 update t0208 set e = e || l_this_lang ||';' where current
> of cur;
> 22 delete from t0208 where current of cur1;
> 23 l_langs := substr(l_langs,instr(l_langs,';') + 1,
> length(l_langs));
>
> 24 end loop;
> 25 end loop;
> 26 end loop;
> 27 end;
> 28 /
>
> PL/SQL procedure successfully completed.
>
> SQL> select * from t0208;
>
> C D E
> ---------- ----------
> --------------------------------------------------
> 1 steve engllish;spanish;german;italian;
> 2 jim english;german;
>
> SQL>
Hi Steve,

Thanks for the great reply. After reading up on cursors more last night i had decided to do something like this.

Thanks again,
J. Received on Fri Feb 09 2007 - 06:07:09 CST