Update a Wordpress database table using Sequel Pro

Written on June 12, 2013 by Flo Preynat

I’m getting more and more requests from bloggers wanting to move from their blogging platforms to a private wordpress environment they will be able to manage as they wish, especially in terms of advertising.

Anyway cut a long story short, as I was testing a database move from Canalblog (which is a french blogging platform) to a local wordpress environment, thanks to an outdated plugin (still my best and only option), I ended up with an ok and usable database with a few things to be sorted by hand.

So what do you do when you don’t know shit about sql and want to mass correct data within your wordpress database tables?

Sequel Pro

First of all, and this is for mac users like me, you need to install Sequel Pro, an open source MySQL database administration tool which can do everything and more. It’s powerful, quick, user friendly (e.g. auto-completion), and it looks a tiny tad better than phpMyAdmin [lol].

Once in Sequel Pro, connect to your database using the MAMP config page, and rapidly browse your tables and various content.

Right so back to my problem. All my posts had been imported with an extra string (“Commentaires sur”) in front of the post title and the post url.

In Sequel Pro, click on the Query button, and query your database:

SELECT post_title, post_name FROM wp_posts where post_title like 'Commentaires sur%'

Test your modified data by adding the function of your choice within your query. Here, I just trim the character string by removing the first 18 characters:

SELECT SUBSTR(post_title, 18), SUBSTR(post_name, 18) FROM wp_posts where post_title like 'Commentaires sur%'

Once you’re happy with it, update your database (here I ran it in two blocks, one for post_title, then for post_name), and make sure you have no errors:

update wp_posts
SET post_title = SUBSTR(post_title, 18)
where post_name like 'commentaires-sur%'

…and voilà, database is updated and your posts are working as expected.

Obviously, depending on the issue, you may need to use a different string function. You’ll find them all here.

My name is Flo Preynat and I am the freelance webdesigner and developer behind shoogle designs. I live in France and specialize in responsive web design. Give me a shoogle or get in touch with me on twitter.

Follow @shoogledesigns