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.