Sql query to publish wordpress post if there is a certain link in the text

Question

MySQL

please tell me how to correctly form a query to the wordpress database,
I need to check in one table whether there are links in a post like letitbit.net (data in an arbitrary field), if there are such links, then in another table that refers to the same post in the post_status field, put publish , that is, publish the entry.
into what I got:
SELECT meta_value FROM wp_postmeta WHERE meta_value LIKE ' http://letitbit.net/% ';
-shows me all posts with a link letitbit.net
UPDATE wp_posts SET post_status = 'publish'
this should change the value of the post to "published"
But I don't understand how to put it together =(.

Answer 1

Try this

UPDATE `wp_posts`  SET `post_status`='publish' WHERE `id` in (SELECT DISTINCT `post_id` as `id` FROM `wp_postmeta` WHERE `meta_value` LIKE '%letitbit.net%')

Answer 2

it is possible, but how to transfer it correctly?

Answer 3

wp_posts table columns:
ID
post_author
post_date
post_date_gmt
post_content
post_title
post_excerpt
post_status
comment_status
ping_status
post_password
post_name
to_ping
pinged
post_modified
post_modified_gmt
post_content_filtered
post_parent
guid
menu_order
post_type
post_mime_type
comment_count
__________
wp_postmeta table columns:
meta_id
post_id
meta_key
meta_key

Answer 4

everything is ok, thanks dude, I don't know what I would do without you!