Solved

Replace data from lookup table dramatically

Posted on 2013-01-10
13
373 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 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
 

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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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 32

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

863 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

26 Experts available now in Live!

Get 1:1 Help Now