Solved

Replace data from lookup table dramatically

Posted on 2013-01-10
13
379 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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

730 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