SQL to Change WordPress User ID Correctly

If you want to change WordPress user ID for some reason (such as security), you not only need to change the ID column in the users table, but you also need to change it in a few other places (such as all the posts and comments made by that user). Otherwise, your WordPress database may get broken and you may see unexpected behavior, such as not being able to log in or losing your user's admin privileges.

This page provides a quick SQL query to change user ID in all the necessary places and maintain your WordPress data integrity.

All the places where user ID must be changed

Changing WordPress user ID is not as simple as changing the ID column in the wp_users table.

User ID can find its way into several other tables throughout the WordPress database:

• wp_usermeta – user_id field, which identifies which user the particular meta record belongs to
• wp_posts – post_author field, which identifies the author
• wp_comments – user_id field, which identifies the comment author (if the comment was made by a registered WordPress user)

You need to change the user ID in all these places, otherwise you risk locking yourself out of WordPress admin or breaking your website.

Updating user ID in wp_users

This part is the most obvious one. The wp_users table contains one row for each user, with details such as ID (the one we want to change), login, email, password, and several others.

The following example (and all the other examples further on this page) is to change a user's ID number from 123 (old user ID) to 456 (new user ID). We will not change any of the other user details – things like email and password will remain the same.

UPDATE `wp_users` 
SET `ID` = 456 
WHERE `ID` = 123;

Updating user ID in wp_usermeta

Another table, which stores additional user details, is wp_usermeta.

This table has multiple rows for each user. Each row stores one meta record, such as first name, last name, or user level. In these rows, the user is identified with user ID in a column named user_id (note that the column name is different from the ID column in wp_users table).

Therefore, we need to change the value in the user_id column (set it to our new user ID = 456) for all rows where this column has the old user ID (123). We must not change any rows for the other users.

UPDATE `wp_usermeta` 
SET `user_id` = 456 
WHERE `user_id` = 123;

Updating user ID in wp_posts

Another place where user ID must be changed is the wp_posts table. For each post, the WordPress database stores ID of the user who created it. The column name is different again: post_author.

Like in the wp_usermeta table, we need to change the value in this column for all rows where its value equals the old user ID.

UPDATE `wp_posts` 
SET `post_author` = 456 
WHERE `post_author` = 123;

Updating user ID in wp_comments

The fourth and final table to update is wp_comments.

The column to change here is user_id (not comment_author – this is different from the posts table).

Comments on WordPress posts can be made by either registered users (who have a user ID in the WordPress database) or unregistered ones (website visitors). In case of the former, the user_id column stores the user ID. In case of the latter, user_id is zero.

Therefore, for the changed user to remain linked to their comments, we must update the user_id column (to 456 in our example) in all rows where its current value equals the old user ID (123).

UPDATE `wp_comments` 
SET `user_id` = 456 
WHERE `user_id` = 123;

Full SQL query to change user ID in all places

UPDATE `wp_users` SET `ID` = 456 WHERE `ID` = 123;
UPDATE `wp_usermeta` SET `user_id` = 456 WHERE `user_id` = 123;
UPDATE `wp_posts` SET `post_author` = 456 WHERE `post_author` = 123;
UPDATE `wp_comments` SET `user_id` = 456 WHERE `user_id` = 123;