Dynamically insert data into MySQL table based on select query result

I was working on a new feature for a site, and while it closely resembles WordPress’ native post formats, it offers more flexibility and control. Users can choose from predefined post layouts and it will display the post using a custom layout and branding.

Since this is a new feature, none of the existing posts have a post layout associated with them, and while there is a default, I actually want my existing posts to be specified.

Here is a MySQL query that performs an insert based on the results of a select query. It’s pretty straightforward, but wanted to share it nevertheless.

1
2
3
4
5
INSERT INTO wp_postmeta(post_id, meta_key, meta_value)
  SELECT ID AS post_id, '_post_layout' AS meta_key, 2 AS meta_value
  FROM wp_posts
  WHERE post_status = 'publish'
  AND post_type = 'post';
INSERT INTO wp_postmeta(post_id, meta_key, meta_value)
  SELECT ID AS post_id, '_post_layout' AS meta_key, 2 AS meta_value
  FROM wp_posts
  WHERE post_status = 'publish'
  AND post_type = 'post';

We basically specify the table (wp_postmeta) and the columns (post_id, meta_key and meta_value) we wish to insert data to, then we select that data from another table, where the post_id, in this case, is dynamic per row, but the meta_key and meta_value are statically set to _post_layout and 2 respectively.

You can adjust this query to your liking, as long as you end up with the desired columns that correspond to the columns you wish to insert data to.

Leave a Reply

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