Menu Close

[MySQL] How to copy column into another table?

Problem

I have a WordPress plugin that saves content as metadata. After a while, I think I should use default place to store that data which is post_content, in wp_posts inside my MySQL database.

I could just create a script that copy those data in loop, but I was thinking is it possible to just use SQL command?

Description

I have 2 tables that look like these

wp_posts

IDTitlepost_contentpost_status
2323Burger is tastypublish
2324Hotdog is tasty toopublish

wp_postmeta

post_idmeta_keymeta_value
2323overviewSome description
2323alttitleAlternative title
2323originaltitleOriginal title
2324overview Some description 2
2324 alttitle Alternative title 2
2324 originaltitle Original title 2

As you can see, the first table (wp_posts) contains empty post_content because I store them in overview under column meta_key inside table wp_postmeta.

Solution

After a few readings, I came into this solution

UPDATE
  wp_posts t1,
  wp_postmeta t2
SET
  t1.post_content = t2.meta_value
WHERE
  t1.ID = t2.post_id
  AND t2.meta_key = overview;

Reference

  1. https://stackoverflow.com/a/224740

Leave a Reply

Your email address will not be published. Required fields are marked *