SFMC Find and Replace via SQL query

Question

I have a data extension where values are imported via an Import Activity. One of those values is a URL ending in .biz. I'm trying to write a query that finds all instances of .biz with .com but encounter an error saying:

"An error occurred while checking the query syntax. Errors: Only SELECT queries are valid. Select must be the first word of the query."

Here's what I wrote:

UPDATE [DataExtension_A]
SET LinkURL = REPLACE(LinkURL, 
'www.url.biz', 'www.url.com')

Is there some other way to find and replaces values in a data extension or is this not possible?

Answer 1

You cannot use UPDATE keywords in Marketing Cloud query activity, to update rows, your data extension must have a primary key column, and you need to use select query to include the Primary key column, and set query update type to "Update".

For example, if the PK column in your data extension is called ID, query should be like below

select ID, REPLACE(LinkURL, 'www.url.biz', 'www.url.com') as 'LinkURL'
from [DataExtension_A]

Answer 2

I think you answer is best answered by the documentation provided by Marketing Cloud: https://help.salesforce.com/articleView?id=mc_as_sql_reference.htm&type=5

Also a previous question sheds more light onto the situation: Insert/ Update new rows from one data extension to another via query

Answer 3

I used a substring to resolve my issue:

Set @url_step_1 = Substring(AttributeValue("UniqueURL"),22)
Set @url_step_2 = Concat("/", @url_step_1)
Set @url_final = concat('www.XXXXXXXXXXXX.XX/XXX',@url_step_2)

Then, anytime I need to link to a page that utilizes the custom URL, I use the following as the destination URL

%%=RedirectTo(@url_final)=%%