Update on table depending on data in same table

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. Received on Thu Feb 08 2007 - 13:12:34 CST