How to bulk edit canonical url’s in SQL?

Down- and uploading CSVs is a tough one that forces you to make a bunch of steps manually which is prone to human error (and typos). My suggestion would be to do something that you can battletest locally in a local copy of your DB. Steps to follow:

1. Make a database dump using the mysqldump tool and use scp to copy it to you locally
2. Pull up a local WP install
3. Install WP CLI, the WP command line runner, locally
4. Run wp post update with the necessary commands locally
5. Test
6. Save your live database by renaming it
7. Upload your local copy of the database to your staging server
8. Test again
9. Make it your adjusted database your live database

Make sure that you have a time where no one fiddles with the database (take your site into maintenance mode!).

Notes:

A) In case you know the IP and port of your MySQL server, you can directly connect to it and pull the dump down to your machine without SSH-ing into remote:

mysqldump -P3306  -h127.0.0.1 -uroot -pYourPassWord databasename > ./dbdumps

Above will dump it in a subfolder of your current directory (pwd) named dbdumps. You should create it up front.

B) Assuming that you have Bash available (command line – sh will still be sufficient enough if you have not), you can use something like the following script. Add it to a file named convert.sh. Also assuming that you got WP CLI installed locally (or your Vagrant machine, Docker container, whatever dev environment you are using locally):

#!/usr/bin/env bash
for id in $(wp post list --field=ID); 
do
    wp post update $id --post_name=foobar
done

As you did not detail that conversion for your posts slug, you will have to find a way to adjust foobar in above example. There are dozens of Stackoverflow answers on the tag:bash topic to help you. As usual sed for the rescue – there’s nothing in the world that can not get fixed with awk and sed in a shell script.

You can run above shell script like the following from your terminal:

sh convert.sh

Just give it a try, fail, rinse and repeat.

I’ve written that mostly out of my head, so you will have to test it. Better make a copy of your local DB so you can trash it start again.