SQLTeam.com | Weblogs | Forums

SQL errors while trying to migrate WordPress


#1

I am currently migrating a WordPress site no a new domain. I don't have any SQL knowledge but I know that I have to change the URL of WordPress using SQL sentences. As far as I know, I have to use this 4 sentences in Php My Admin.

  1. UPDATE wp_options SET option_value = REPLACE ( option_value, ‘olddomain.com’, ‘newdomain.com’ );
  2. UPDATE wp_posts SET guid = REPLACE ( guid, ‘olddomain.com’, ‘newdomain.com’ );
  3. UPDATE wp_posts SET post_content = REPLACE ( post_content, ‘olddomain.com’, ‘newdomain.com’ );
  4. UPDATE wp_postmeta SET meta_value = REPLACE ( meta_value, ‘olddomain.com’, ‘newdomain.com’ );

The problem is that I get an error message when I try the first sentence: UPDATE wp_options SET option_value = REPLACE ( option_value, ‘http://s394706415.mialojamiento.es/tg’, ‘http://tecnigap.eu/tg’ )

The error is: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax.

If I use the same sentence without the http://, i get this error: #1054 - Unknown column '‘s394706415.mialojamiento.es' in 'field list'

As I told, I have no knowledge about SQL. I just designed a web in WordPress and no have to change its domain.

Thanks!


#2

This is a Microsoft SQL Server forum, so you might find that people here are not very familiar with MySQL.

UPDATE wp_options 
SET option_value = REPLACE ( option_value, 
          ‘http://s394706415.mialojamiento.es/tg’, 
          ‘http://tecnigap.eu/tg’ )

looks fine to me (syntax-wise), except that I would have expected

UPDATE wp_options SET option_value = REPLACE ( option_value, ‘olddomain.com’, ‘newdomain.com’ );

to mean

UPDATE wp_options 
SET option_value = REPLACE ( option_value, 
          ‘s394706415.mialojamiento.es’, 
          ‘tecnigap.eu’ )

but if the original data includes the "http://" bit, and the trailing Path, then the REPLACE would still work OK.

REPLACE changes parts-of-columns hence why I think the code is intended to only change the Domain Name part. That said, if you ask REPLACE to change a string that exactly matches the whole contents of a column it will happily do that, its just a bit overkill at that point!

MySQL sometimes uses funny quote characters for things - like the back-tick, but if you have an exact copy of the 1...4 instructions and Copy & Paste the quote from that I can't see you going wrong.

I don't know if MySQL requires the ";" on the end of the line, but I not that you don't have that on the end of your code as you posted it.

Either way, I suspect you will be better off on a MySQL or WordPress forum :smile:


#3

Ah, that's interesting. Posting that (i.e. formatted using the [</>] button) it looks like your code has curved single-quotes rather than straight-ones.

Try putting straight-quotes instead of curved "speech-marks"


#4
UPDATE wp_options 
SET option_value = REPLACE ( option_value, 
          ‘s394706415.mialojamiento.es’, 
          ‘tecnigap.eu’ )

should be

UPDATE wp_options 
SET option_value = REPLACE ( option_value, 
          's394706415.mialojamiento.es', 
          'tecnigap.eu' )

#5

Kristen, you are AWESOME.

Thanks :smile: