How do I change the user via SQL?

Question

I change a user via SQL in wordpress:

SET @old_user='old_user';
SET @new_user='new_user';

UPDATE wp_users SET user_login = replace(user_login, @old_user, @new_user);
UPDATE wp_users SET user_nicename = replace(user_nicename, @old_user, @new_user);
UPDATE wp_usermeta SET meta_value = replace(meta_value, @old_user, @new_user);

However, while posting a new one, the author is still the old one, for instace,

Posted on October 3, 2015 by old_user.

I must ignore something, please guide me.

Answer 1

You probably haven't changed the display name, which is probably what is being displayed. "nicename" is used for the author's posts page url and not for display.

UPDATE wp_users SET display_name = replace(display_name, @old_user, @new_user);

Answer 2

I know this is an old question, but I needed to do this to migrate a WordPress user (#38 to user #1) and so I'm posting updated code below. You should also have a WHERE clause to be safer;

In the following we are replacing USER #38 to USER #1. This is similar to merging the two together, but beware that all usermeta data for USER #1 (if exists) will be over-written with user 38's info and afterwards, there will no longer be any usermeta for USER #38 since it is now effectively moved to user_id #1. This only affects the usermeta table, so if you are merging users, you'd also need to modify the user table. I used phpmyadmin / MariaDB.

 UPDATE `wp_usermeta` SET `user_id` = REPLACE (`user_id`, 38, 1) WHERE `user_id` = 38;

Equally if we want to change the Author of posts from USER#38 to USER#1

  UPDATE `wp_posts` SET `post_author` = REPLACE (`post_author`, 38, 1) WHERE `post_author` = 38;

Answer 3

best is to update it via your Phpmyadmin.. easy select your database and then select users table amd just rename what you need..