Search code examples
mysqlsqlsubquerysql-insert

How to insert record where inner joined table is null?

I have a main table which contains several Wordpress post types like:

ID | post_title | post_type
 1      foo         zoacres-property
 2      foo2        zoacres-property
 3      foo3        post

the post with post_type as zoacres-property contains a specific meta value located in the wp_postmeta table:

meta_id | post_id | meta_key | meta_value
  100       2         price       5000

as you can see the post with id 1 doesn't have the meta key price.

Is there any way to add the meta_key with the price at 0 in the posts that are missing?

Solution

  • You can use a left join with insert

    insert into meta (`post_id`, `meta_key`, `meta_value`)
select p.ID, 'price', 0
from post p
left join meta m on p.ID = m.post_id and m.meta_key = 'price'
where p.post_type = 'zoacres-property'
      and m.post_id is null

    DEMO