Re: Update on table depending on data in same table

From: Steve Howard <stevedhoward_at_gmail.com>
Date: 8 Feb 2007 12:54:20 -0800
Message-ID: <1170968060.876403.123000@a34g2000cwb.googlegroups.com>

On Feb 8, 2:30 pm, "Steve Howard" <stevedhow..._at_gmail.com> wrote:
> On Feb 8, 2:12 pm, "homerjk" <jonathan.kee..._at_gmail.com> wrote:
>
>
>
> > Hey everyone,
>
> > I'm still a relative newb so go easy please!
>
> > I've inherited a system from someone that is in a bit of a mess and I
> > need to tidy it up. It's an Oracle 10g database.
>
> > The part i have to clean up basically has two tables.
> > Table A attributes are: SOURCEID, NAME, DESCRIPTION, DEPARTMENT,
> > LANGUAGES.
> > Table B attributes are: SOURCEID, LANGID, ROLE, STATUS.
>
> > Now the way this system is supposed to work is that a row is entered
> > into table A and X amount of languages are added to the LANGUAGES
> > field. This field is then split up into a row for each language into
> > table B with the corresponding SOURCEID.
> > e.g.
> > Table A:
> > 123 -- Homer -- lazy -- HR -- French;Spanish;German
>
> > Table B:
> > 123 -- French -- manager -- approved
> > 123 -- Spanish -- admin -- pending
> > 123 -- German -- teaboy -- proposed
>
> > This system had been working fine until a new person started and
> > instead of just adding a new language to the original row in table A
> > she added 1000+ rows that are identical to those already in the table
> > except for the fact the language is Japanese. So now the table looks
> > like
> > Table A:
> > 123 -- Homer -- lazy -- HR -- French;Spanish;German
> > 456 -- Homer -- lazy -- HR -- Japanese;
>
> > Table B:
> > 123 -- French -- manager -- approved
> > 123 -- Spanish -- admin -- pending
> > 123 -- German -- teaboy -- proposed
> > 456 -- Japanese -- janitor -- approved
>
> > I have been given the unenviable task of fixing this by, identifying
> > all terms that have identical attributes (except for the LANGUAGES
> > attribute), changing the SOURCEID of the duplicate in table B to the
> > master term SOURCEID (so in the example above, in table B change the
> > row 456 -- Japanese -- janitor -- approved to 123 -- Japanese --
> > janitor -- approved), tagging on Japanese onto the master term (in the
> > example above, in table A change the row 123 -- Homer -- lazy -- HR --
> > French;Spanish;German to 123 -- Homer -- lazy -- HR --
> > French;Spanish;German;Japanese;) and then deleting the duplicate entry
> > in table A (in the example above, delete the row 456 -- Homer -- lazy
> > -- HR -- Japanese;)
>
> > Thanks for sticking with me this far (if you have!). Luckily enough I
> > know for a fact that all the master terms are in the SOURCEID range 1
> > to 2000 and the duplicates are in the SOURCEID range 2500 to 6000. so
> > identifying the terms is not a problem. I just ran an intersect query
> > and found my ~1000 culprit terms. As you probably know though, from
> > this I am not getting the SOURCEIDS of either row so basically i have
> > been getting the SOURCEIDS of the identified terms and running the
> > following command manually.
>
> > update target set target.SOURCEID=MASTER_NUMBER where
> > target.SOURCEID=DUPLICATE_NUMBER;
> > update source set
> > source.LANGSREQUIRED='French;Spanish;German;Italian;Brazilian
> > Portuguese;Simplified Chinese;Traditional Chinese;Swedish;French
> > Canadian;Dutch;Korean;Japanese;' where source.SOURCEID=MASTER_NUMBER;
> > DELETE FROM source WHERE sourceid = DUPLICATE_NUMBER;
>
> > As you can tell my wrists are about to fall off with the RSI of
> > continually trying to find out the IDs and then run these commands.
> > SQL is not really my game so i am not sure whether i would be able to
> > use some procedure or cursor or something along those lines... I have
> > already wasted an awful lot of time trying to read of some way to help
> > me by running different commands but i am very quickly running out of
> > time...
>
> > Any help at all would be appreciated or even just a big fat no to say
> > you're gonna have to put up with it and just do it manually.
>
> > Thank you reading all this and I hope that I have explained it clearly
> > enough,
> > Jonathan.
>
> Hi Jonathan,
>
> What if you have something like the following?
>
> 123 -- Homer -- lazy -- HR -- French;
> 456 -- Homer -- lazy -- HR -- Japanese;
>
> Which one is correct?
>
> Regards,
>
> Steve

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> Received on Thu Feb 08 2007 - 14:54:20 CST