CODEWITHSUNDEEP
sqlreplacephpmyadmin
danielassayag
danielassayag

Reputation: 941

Replace SQL string in all tables at once

My goal is to add www to addresses, replacing http://mywebsite.org with http://www.mywebsite.org in all tables at once.

I have tried the following, but it doesn't seem to work:

SELECT Replace('http://mywebsite.org', 'mywebsite.org', 'www.mywebsite.org')

Any help?

new edit:

i try to change this in wp_option, its a option_value string: 

UPDATE wp_options SET option_value = 'http://www.iemj.org' WHERE option_value = 'http://iemj.org'

-- doesnt change

 a:4:{s:25:"dashboard_recent_comments";a:1:{s:5:"items";i:5;}s:24:"dashboard_incoming_links";a:5:{s:4:"home";s:19:"http://www.iemj.org";s:4:"link";s:95:"http://blogsearch.google.com/blogsearch?scoring=d&partner=wordpress&q=link:http://www.iemj.org/";s:3:"url";s:124:"http://blogsearch.google.com/blogsearch_feeds?scoring=d&ie=utf-8&num=10&output=rss&partner=wordpress&q=link:http://iemj.org/";s:5:"items";i:10;s:9:"show_date";b:0;}s:17:"dashboard_primary";a:7:{s:4:"link";s:26:"http://wordpress.org/news/";s:3:"url";s:31:"http://wordpress.org/news/feed/";s:5:"title";s:14:"WordPress Blog";s:5:"items";i:2;s:12:"show_summary";i:1;s:11:"show_author";i:0;s:9:"show_date";i:1;}s:19:"dashboard_secondary";a:7:{s:4:"link";s:28:"http://planet.wordpress.org/";s:3:"url";s:33:"http://planet.wordpress.org/feed/";s:5:"title";s:20:"Other WordPress News";s:5:"items";i:5;s:12:"show_summary";i:0;s:11:"show_author";i:0;s:9:"show_date";i:0;}}

Upvotes: 1

Views: 9825

Answers (3)

Christopher
Christopher

Reputation: 2316

I know this is a bit old and this question may have a duplicate by now but I found/use this from time to time for similar reasons so thought I would share. Bit slow for larger databases but works great IMO. I definitely would recommend you wrap in BEGIN/ROLLBACK TRANSACTION first though to be sure it works/is safe first: https://gist.github.com/1e76349ce8fa0d60459b58394999d749

Upvotes: 0

WattoWatto
WattoWatto

Reputation: 159

It has to be in Update statement to change the dataset.

If its singular it should be;

UPDATE <table_name> SET <field_name>='http://www.mywebsite.org' WHERE <field_name> = 'http://mywebsite.org'

Mass Changing to add 'www' after 'http://';

Please do back up before you do this, especially you rely on this dataset

 UPDATE <table_name> SET <field_name>=Replace(<field_name>, replace(<field_name>, 'http://', ''), 'www.'+replace(<field_name>, 'http://', '')) WHERE <field_name> LIKE 'http://%'

Source

Upvotes: 1

Paul
Paul

Reputation: 1059

SELECT is the command used to return information from SQL. You're seeking to UPDATE information. You have also not specified what table the information is FROM or what column it is stored in. e.g.

UPDATE tablename
SET columnname = REPLACE(columnname, 'http://mywebsite.org', 'http://www.mywebsite.org')

I recommend you backup your database before playing around in this manner.

Upvotes: 2

Related Questions