Add commentmeta custom field to every published comment with SQL

Question

I am trying to add custom commentmeta "likes" with 0 as default value to every posted comment. I have this query but I dont see any error there, but still it doesnt work. Can anyone help me to solve this issue? :) thanks!

INSERT INTO wp_commentmeta( comment_id, meta_key, meta_value )
SELECT comment_ID, 'likes' AS meta_key, 0 AS meta_value
FROM wp_comments WHERE comment_approved = '1'

Answer 1

I assume you're already properly using the WP Metadata API https://codex.wordpress.org/Metadata_API to attach the field to new comments and you just want to backfill for existing comments by stuffing the database?

If it was my problem, I wouldn't bother and would assume in my code that absence of meta implied no likes. If you use update_meta_data() to update the values when users like a comment, then it will create the meta key,value pair if it doesn't exist.

But, to answer your actual question, you don't want the ASs. This works for me:

INSERT INTO wp_commentmeta( comment_id, meta_key, meta_value ) SELECT comment_ID, 'likes', 0 FROM wp_comments WHERE comment_approved = '1';