Update Links in WordPress with PHPMyAdmin

vim2-html

Often times during site migrations you may need to do bulk updates to links or other content to many pages at once.  Using phpmyadmin is a great way to accomplish this.

A general snippet would be:

UPDATE wp_posts SET post_content = REPLACE(post_content, 'whatyouhave', 'whatyouwant')

For example,
UPDATE wp_posts SET post_content = REPLACE(post_content, 'http://', 'https://')

Would update all http links to https links

and

UPDATE wp_posts SET post_content = REPLACE(post_content, '/subdomain/sub2/', '/newdomain/new2/')

Would update urls from one path to another.

Using CSV Files to Update Database Tables with phpMyAdmin

vim2-html

For many reasons either when moving between different cms systems perhaps or for more general reasons it is useful to import data with csv files.  Often this is because it is easier to manipulate the data in a spreadsheet and then create mass updates.

phpMyAdmin doesn’t allow you to update data from a CSV file directly.  You can import the data into an empty table, but you cannot update existing records.  To get around this, you can follow these steps:

  1. Export the data that you need from the current table – in part so you can see how it is structured.
  2. Then edit this file in a spreadsheet program.  After you changes are finished then export the file as csv.  In this example we are updating references to page urls in a table “pages” and our data file has simply id and a url instead of all the data.
  3. In phpmyadmin create a new temporary table
    CREATE pages_url_update LIKE pages (which would create a replica -- or just create the columns you need -- in this example an id and a url)
  4. Open that table in phpMyAdmin and then choose “import”
    — Select the csv to upload
    — And choose CSV Using LOAD DATA under Format
  5. Then select the true data table and click on the sql button
  6. Then do
    UPDATE pages
    INNER JOIN pages_url_update on pages_url_update.id = pages.id
    SET pages.url = pages_url_update.url
  7. Then drop the temporary table – DROP pages_url_update