Question 2568 in Wordpress Question posted in
The official Wordpress documentation can be found here.

MySQL query for Batch Change Author of WordPress Posts

I would like to change the author name of all the posts I have in WordPress (+1000 articles). Currently I have the same author for all posts (Johnny user, your id=1). The thing is that I would like to run a MySQL query to randomly change the author of the post to another user. Currently I have 10 users that I would like to put as authors of the posts, randomly, in such a way that each one should be as the author of about 100 posts.

I know I can change all the authors like this, but it is far from what I want to achieve:

UPDATE wp_posts SET post_author='2' WHERE post_author='1';
Tags:

2

Answers


  1. 0

    I can’t write you a working query but you can try it manually like this:

    My approach would be to first get all IDs of the posts you want to change the autor of – simply because you’d have to include them in the WHERE too.

    Then you can use Excel or LibreOffice Calc for example, I used it several times for similar tasks and you can generate a line of the SQL query divided into parts/columns, where the part with post ID would be a separate column that you’d fill with the post IDs from the export. You can randomize the order of them in some online utility or text editor.

    Then you fill/copy the rest of the columns with the post author IDs – like you have 10, so let’s say you have 2000 posts/post IDs, so first 200 would have author ID "1", another 200 author ID "2 and so on.

    So at the end you’d have 2000 rows in Excel that would look like:

    UPDATE wp_posts SET post_author='2' WHERE post_author='1' AND post_id='1';
UPDATE wp_posts SET post_author='2' WHERE post_author='1' AND post_id='50';
UPDATE wp_posts SET post_author='2' WHERE post_author='1' AND post_id='145';
UPDATE wp_posts SET post_author='2' WHERE post_author='1' AND post_id='14';
...
UPDATE wp_posts SET post_author='3' WHERE post_author='1' AND post_id='16';
...

    Not sure of the syntax, but only to show the idea….

    When you generated these lines you can export them back from Excel to txt file, maybe correct the syntax by deleting some extra spaces, depends on the export and you can try it after backup.

    I’m fully aware this can be done in some script way, but I think this will also work.

    Login or Signup to reply.
  2. 0

    Let’s step through the requirements:

    So now we just need to incorporate that into an UPDATE. Something like this should do the trick:

    UPDATE wp_posts SET post_author = FLOOR(1 + RAND() * 9);

    UPDATE

    Actually a search for mysql update random turns up another question answering this question already, and I have voted to close this as a duplicate.

    UPDATE all records with a random number from a range

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search