I am trying to change a domain name in more than one field in a database table, but am getting a bit confused as to the syntax
I need to change a table called breadcrumbs
and change
http://oldurl.com to http://newurl.com in each entry within the table.
There is of course a full URL after the domain.
The URL appears in two columns.
UPDATE `breadcrumbs`
SET `column` = REPLACE(`column`, 'http://oldurl.com', 'http://newurl.com');
Do this for every column in which you want to replace the string.
See String Functions: REPLACE() in the MySQL manual.
UPDATE breadcrumbs
SET col1 = REPLACE(col1, 'http://oldurl.com', 'http://newurl.com'),
col2 = REPLACE(col2, 'http://oldurl.com', 'http://newurl.com')
update breadcrumbs
set url1 = replace(url1, 'oldurl.com', 'newurl.com'),
url2 = replace(url2, 'oldurl.com', 'newurl.com')
where url1 like 'http://oldurl.com%'
or url2 like 'http://oldurl.com%'
A simple way is to use two updates:
update breadcrumbs
set url1 = concat('http://newurl.com', substr(url1, 18))
where url1 like 'http://oldurl.com%';
update breadcrumbs
set url2 = concat('http://newurl.com', substr(url2, 18))
where url2 like 'http://oldurl.com%';
You can do this in one update as well:
update breadcrumbs
set url1 = (case when url1 like 'http://oldurl.com%'
then concat('http://newurl.com', substr(url1, 18))
else url1 end),
url2 = (case when url2 like 'http://oldurl.com%'
then concat('http://newurl.com', substr(url2, 18))
else url2 end)
where url1 like 'http://oldurl.com%' or
url2 like 'http://oldurl.com%';
