Insights

Dynamically insert data into MySQL table based on select query result

Ryan Sechrest

Mar 28, 2013 • 1 min read

A beautiful MySQL table. Get it?

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.

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.

Comments (1)