H-SC
asked on
Replace using Select
I have a table called REPLACE_ITEMS. It has two columns called old_value, new_value
I would like to write a replace statement to cross reference the values from the table
for example
in a table called ADDRESS I have a column called addr_1
I would like to run the replace statement against addr_1 cross referencing all possibilities from the REPLACE_ITEMS table.
Here is a scenario of what something would look like in the address table before and after:
The replace query would find "rd" in the replace_items.old_value and replace it with the replace_items.new_value of "Road"
OLD ............. 4101 test rd
NEW.............. 4101 test Road
the question: How would the replace update query be written?
Thanks in Advance!
I would like to write a replace statement to cross reference the values from the table
for example
in a table called ADDRESS I have a column called addr_1
I would like to run the replace statement against addr_1 cross referencing all possibilities from the REPLACE_ITEMS table.
Here is a scenario of what something would look like in the address table before and after:
The replace query would find "rd" in the replace_items.old_value and replace it with the replace_items.new_value of "Road"
OLD ............. 4101 test rd
NEW.............. 4101 test Road
the question: How would the replace update query be written?
Thanks in Advance!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Phillip,
Many thanks for explaining the DQS option, I was aware that newer versions had this feature, but was looking for more of a query approach.
Many thanks for explaining the DQS option, I was aware that newer versions had this feature, but was looking for more of a query approach.
ASKER
awking00,
Your solution works great! Many thanks
Your solution works great! Many thanks
Glad to help.
Otherwise, you are doing a cursor, opening the replace_items and then updating addr_1 wherever it appears (you can test for that using the Replace function).