Solved

Replace data from lookup table dramatically

Posted on 2013-01-10
13
372 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
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 73

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 39

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 39

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
 

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 73

Expert Comment

by:sdstuber
ID: 38764421
ged325 - I believe this is an oracle question based on the create table syntax.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 73

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 31

Expert Comment

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

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 73

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 73

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video discusses moving either the default database or any database to a new volume.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now