?
Solved

Replace data from lookup table dramatically

Posted on 2013-01-10
13
Medium Priority
?
388 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

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 2000 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

Independent Software Vendors: 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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
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…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

764 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