1

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.

  • 1
    just write a proper php code which uses the WP API for that – Mark Kaplun Jun 27 '16 at 15:32
0

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);
}
  • Hi @majick thank you for your answer. I've found a similar snippet, however, it brings me back to using PHP code which is what I was looking to avoid. There are also a couple of SQL solutions using a temp table which is very close to yours. At the end, as I needed to get the job done quickly, I created a complete function in PHP with a slightly different logic, nevertheless, the question remains as I would really like to understand what's the proper SQL syntax to perform the UPDATE – Nico Pernice Jun 28 '16 at 14:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.