Solved

Replace data from lookup table dramatically

Posted on 2013-01-10
13
381 Views
Last Modified: 2013-01-11
Gurus,                                                                                      
                                                                                             
I'm trying to create a dramatic way to replace data from a lookup table.                    
                                                                                             
This works like a champ:                                                                    
                                                                                             
update PRO set PROVF5C = replace(PROVF5C,'cell','mobile')                                    
                                                                                             
However, I want to store all the values in a lookup table and have it be completely dramatic.
                                                                                             
Example:                                                                                    
                                                                                             
 CREATE TABLE LOOKUP_TABLE                                                                  
   (tabkey NUMBER(12),                                                                      
      lankeyi NUMBER(12),                                                                        
      words VARCHAR2(250));                                                                      
                                                                                             
                                                                                             
insert into lookup_table values (1,1,'cell');                                                
insert into lookup_table values (1,13,'mobile');                                            
insert into lookup_table values (2,1,'color');                                              
insert into lookup_table values (2,13,'colour');                                            
                                                                                             
select * from lookup_table;                                                                  
TABKEY          LANKEYI                 WORDS                                                
1                    1                             cell                                                
1                    13                             mobile                                            
2                    1                             color                                              
2                    13                             colour                                            
                                                                                             
                                                                                             
Before:                                                                                      
LOOKUP_TABLE.WORDS = 'mobile'                                                                
PRO.PROVF5C = 'My cell phone is dead'                                                
                                                                                             
What is the best option for replacing data in my PRO table with data from LOOKUP_TABLE?      
                                                                                             
After:                                                                                      
PRO.PROVF5C = 'My mobile phone is dead'                                              
                                                                                             
Any help would be appreciated
0
Comment
Question by:xbox360dp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 38764170
BEGIN
    -- loop through old words
    FOR old IN (SELECT *
                  FROM lookup_table
                 WHERE lankeyi = 1)
    LOOP
        -- For each old word, find the corresponding new word and update the table
        UPDATE pro
           SET provf5c      =
                   REPLACE(provf5c,
                           old.words,
                           (SELECT words
                              FROM lookup_table
                             WHERE lankeyi = 13 AND tabkey = old.tabkey)
                          );
    END LOOP;
END;
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 38764357
No loops necessary, just SQL 2005 or above.

NOTE: I changed the lookup table so w was the word to search for and V is the replacement word.

-- temp tables.
select 1 id,  cast ('my color cell phone is dead' as varchar(100)) comment into #pro
insert into #pro
select 2, 'check out the color'


select 1 tabkey, cast ('cell' as varchar(20)) w , 'mobile' v
into #lkup

insert into #lkup select 2, 'color',  'colour'

-- BEFORE
select * from #pro
select * from #lkup


-- update
update MSTR
set
comment = new_comment
from
#pro MSTR
join
(

select

p.id,
(

            select
            STUFF((SELECT ' ' + newword
            FROM
            (
            select id, occurrence, isnull(REPLACE(a.display_term, a.display_term, lk.v), a.display_term) newWord    
            from #pro m
            
            cross apply
             sys.dm_Fts_parser('"' + comment + '"' , 1033, 0,0) a
             left join #lkup lk on a.display_term = lk.w
            where m.id = p.id
            ) a

            FOR XML PATH('')), 1, 1, ''
            )

) new_comment


from #pro p

)old

on old.ID = MSTR.ID


-- AFTER
select * from #pro

-- CLEANUP
drop table #pro
drop table #lkup
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 38764379
One other note, I used  sys.dm_Fts_parser as I didn't have a split function on the current DB I'm working on.

see here:
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_26425025.html

for a split function if you want to avoid using the system function.
0
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 

Author Comment

by:xbox360dp
ID: 38764406
Hi Stuber,

This is great!

However, I will need to put this in a trigger and the PRO table is the table being used.

Ideas?
0
 

Author Comment

by:xbox360dp
ID: 38764415
Thanks for the input ged325 but this is in Oracle.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38764421
ged325 - I believe this is an oracle question based on the create table syntax.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38764437
assuming you are working with a before each row trigger, then the body of the trigger would look something like this...

BEGIN
    -- loop through old words
    FOR old IN (SELECT *
                  FROM lookup_table
                 WHERE lankeyi = 1)
    LOOP
        -- For each old word, find the corresponding new word and update the table
          :new.provf5c := REPLACE(:new.provf5c,
                           old.words,
                           (SELECT words
                              FROM lookup_table
                             WHERE lankeyi = 13 AND tabkey = old.tabkey)
                          );
    END LOOP;
END;


same as above, but instead of issuing an update on the table, you update the :new  pseudo-record
0
 
LVL 32

Expert Comment

by:awking00
ID: 38764741
What dbms are you using?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38764766
Oracle -  http:#a38764415
0
 

Author Comment

by:xbox360dp
ID: 38764936
Sdstuber,

Looks like there is a syntax issue ...

 :new.provf5c := REPLACE(:new.provf5c,
                           old.words,
                           (SELECT words

                              FROM lookup_table
                             WHERE lankeyi = 13 AND tabkey = old.tabkey)
                          );

Is this possible to do with the replace function?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38764995
I'm using the replace function  but, I messed up the usage when I took it out of a sql update and put in the trigger assignment.

Try this  (this one I actually tested)


CREATE OR REPLACE TRIGGER trg_replace_words
    BEFORE INSERT OR UPDATE
    ON pro
    FOR EACH ROW
BEGIN
    -- Loop through old words
    -- For each old word, find the corresponding new word and update the table
    FOR replacment IN (SELECT o.words old_word,
                              (SELECT words
                                 FROM lookup_table n
                                WHERE n.lankeyi = 13 AND n.tabkey = o.tabkey)
                                  new_word
                         FROM lookup_table o
                        WHERE lankeyi = 1)
    LOOP
        :new.provf5c  := REPLACE(:new.provf5c, replacment.old_word, replacment.new_word);
    END LOOP;
END;
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 38765037
you could also write the trigger with the for loop cursor like this , which might be better since it will only replace words where it can find a match,  whereas the version above will replace them with NULL, which is the same as removing them from the phrase

CREATE OR REPLACE TRIGGER trg_replace_words
    BEFORE INSERT OR UPDATE
    ON pro
    FOR EACH ROW
BEGIN
    -- Loop through old words
    -- For each old word, find the corresponding new word and update the table
    FOR replacment IN (SELECT o.words old_word, n.words new_word
                         FROM lookup_table o, lookup_table n
                        WHERE o.lankeyi = 1 AND n.lankeyi = 13 AND n.tabkey = o.tabkey)
    LOOP
        :new.provf5c  := REPLACE(:new.provf5c, replacment.old_word, replacment.new_word);
    END LOOP;
END;
0
 

Author Closing Comment

by:xbox360dp
ID: 38767655
Excellent, works like a champ!
0

Featured Post

Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question