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.

0
, , , Nico Pernice 4 years 0 Answers 48 views 0

Leave an answer