20110814 bulk change in wordpress post author - plembo/onemoretech GitHub Wiki
title: Bulk change in WordPress post author link: https://onemoretech.wordpress.com/2011/08/14/bulk-change-in-wordpress-post-author/ author: lembobro description: post_id: 826 created: 2011/08/14 21:48:44 created_gmt: 2011/08/15 01:48:44 comment_status: closed post_name: bulk-change-in-wordpress-post-author status: publish post_type: post
Bulk change in WordPress post author
One of the really fun things about converting from one blog system to another is the cleanup. Today I decided to "fix" the name shown as post author on both my blogs. The initial import set this to "admin", of course. Now I wanted to change it to "eldapo", but without a lot of repetitive gui point, pull and click. The trick was to just drop to the command line and do everything against the MySQL database directly. First thing to do was log in to the database as the designated application user. `
mysql -u wpdb1 -p wpdb1
Next, I had to figure out what field in each post contained the author string, by listing the database tables and picking a likely suspect.
mysql> show tables;
+-------------------------+
| Tables_in_wpdb1 |
+-------------------------+
| wp_commentmeta |
| wp_comments |
| wp_links |
| wp_options |
| wp_postmeta |
| wp_posts |
| wp_term_relationships |
| wp_term_taxonomy |
| wp_terms |
| wp_usermeta |
| wp_users |
+-------------------------+
11 rows in set (0.00 sec)
Looked to me like wp_users and wp_usermeta were the best bets. I first did a greedy select on wp_users:
mysql> select * from wp_users;
This yielded some nice info, including the user_login string. I then did the same against the wp_usermeta table. This got me some more detailed information on each user, including the fact that the user_id value for admin was '1' and for eldapo was '2'.
mysql> describe wp_posts;
+-----------------------+---------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------------+------+-----+---------------------+----------------+
| ID | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| post_author | bigint(20) unsigned | NO | MUL | 0 | |
| post_date | datetime | NO | | 0000-00-00 00:00:00 | |
| post_date_gmt | datetime | NO | | 0000-00-00 00:00:00 | |
| post_content | longtext | NO | | NULL | |
| post_title | text | NO | | NULL | |
| post_excerpt | text | NO | | NULL | |
| post_status | varchar(20) | NO | | publish | |
| comment_status | varchar(20) | NO | | open | |
| ping_status | varchar(20) | NO | | open | |
| post_password | varchar(20) | NO | | | |
| post_name | varchar(200) | NO | MUL | | |
| to_ping | text | NO | | NULL | |
| pinged | text | NO | | NULL | |
| post_modified | datetime | NO | | 0000-00-00 00:00:00 | |
| post_modified_gmt | datetime | NO | | 0000-00-00 00:00:00 | |
| post_content_filtered | text | NO | | NULL | |
| post_parent | bigint(20) unsigned | NO | MUL | 0 | |
| guid | varchar(255) | NO | | | |
| menu_order | int(11) | NO | | 0 | |
| post_type | varchar(20) | NO | MUL | post | |
| post_mime_type | varchar(100) | NO | | | |
| comment_count | bigint(20) | NO | | 0 | |
+-----------------------+---------------------+------+-----+---------------------+----------------+
23 rows in set (0.01 sec)
This revealed that one of the wp_post fields was post_author.
mysql> select post_author from wp_posts where ID = '1';
This showed that post_author was a numeric value, then set to '1'. Knowing the user_id value from wp_usermeta, and that it corresponded to post_author in wp_posts, I ran this command against the wp_posts table to effect the desired change:
mysql> update wp_posts
-> set post_author = '2'
-> where post_author = '1';
` And, voila! All my posts are now authored by eldapo!
Copyright 2004-2019 Phil Lembo