mysql - WordPress MySQL query: copy data from a table to another

Question

So I'm using a wordpress plugin for a while to add a "Sources" custom field into the end of each post. The thing is this plugin isn't updated for 4 years and I want to get rid of it. But I can't afford to lose the saved sources for each post. I could manually edit these posts one by one, but after looking at the database with the query below I found that theres over 19k posts using this sources plugin.

SELECT *
FROM `wp_postmeta`
WHERE `meta_key` = 'sa_source'
AND `meta_value` <> ''

The sources are stored in wp_postmeta table, meta_value column. As I'm not great at SQL, I'm here looking for some help. I need to insert the content from:

• wp_postmeta table, meta_value column. Where meta_value is not empty

into the end of:

• wp_posts table, post_content column. Where the post_id from wp_postmeta is the same as ID from wp_posts

标签： mysqlsql

Answer 1

下一个查询可以解决您的问题：

    update
      wp_posts
    join 
      wp_postmeta on wp_postmeta.post_id = wp_posts.post_id
    set
      -- add mata_value content to current post_content with one space
      post_content = concat(post_content, ' ', meta_value)
    where 
      wp_postmeta.meta_value is not null and wp_postmeta.meta_value <> '';