UPDATE on SELECT results. A SQL query to swap Author with Meta Box value

Question

I inherited a project where, for some reason, the previous developer make a custom post type for authors and a custom select listing all those authors cpt titles in the post edit screen. This led to a lot of additional code to handle permalinks, templates, edits, and custom queries while all of this could be achieved out of the box.

So, I've already found a way to create all the missing users out of the author cpts. Now I am looking for an SQL command or script to swap the regular author with the new one based on the mata_key value. For some posts however, this value could be null.

With this SQL query I group all the posts with a certain fake_author id:

SELECT ID, post_title FROM wp_posts JOIN wp_postmeta a ON ( a.post_id = wp_posts.ID AND a.meta_key='fake_author' AND a.meta_value = '111111');

How can I perform an update in the post_author column for the resulting posts?

My attempt so far: UPDATE wp_posts INNER JOIN wp_postmeta a ON ( a.post_id = wp_posts.ID AND a.meta_key='fake_author' AND a.meta_value=111111) SET post_author=2222222;

However, this gives no results.

Answer 1

I always find it way less confusing to get the results from one table and loop them rather than to try to join them like that... eg.

global $wpdb;
$query = "SELECT post_id FROM wp_postmeta WHERE meta_key='fake_author' AND meta_value='11111'";
$results = $wpdb->get_results($query);

foreach ($results as $result) {
    $query = "UPDATE wp_posts SET post_author='222222' WHERE ID='".$result->post_id."'";
    $wpdb->query($query);
}