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.

--------------------------------------------------

>>> Share a link to this question, just copy and paste the code bellow on your web page <<<

<a href="https://www.wptricks.com/question/update-on-select-results-a-sql-query-to-swap-author-with-meta-box-value/">

UPDATE on SELECT results. A SQL query to swap Author with Meta Box value</a>
0
, , , Nico Pernice 4 years 0 Answers 14 views 0

Leave an answer