SQL queries.txt

## Find and replace ##

update wp_posts set post_content =
replace( post_content, '<h2 id="h-', '<h2 id="' );


## Change metakey ##

UPDATE
	wp_postmeta
SET
	meta_key = '_post_color'
WHERE
	meta_key = '_prefix_post_color';

## Change metakey with specific length ##

UPDATE
	wp_postmeta
SET
	meta_key = '_yoast_wpseo_linkdex'
WHERE
	meta_key = '_yoast_wpseo_metadesc'
AND LENGTH(meta_value) = 2

## Get comments with "done" value ##

SELECT
	  wp_posts.ID,
	  wp_posts.post_title,
	  wp_comments.comment_content
FROM
	  wp_comments
LEFT JOIN
	wp_posts
ON
	  wp_comments.comment_post_ID = wp_posts.ID
WHERE
	  wp_comments.comment_content LIKE '%"done"%
AND
	  wp_comments.comment_date >= 20200101

## Select posts with specific meta values ##

SELECT
	wp_posts.ID,
	wp_posts.post_title,
	wp_posts.menu_order
FROM
	wp_posts
INNER JOIN
	wp_postmeta
ON
	wp_posts.ID = wp_postmeta.post_id
WHERE
	1=1
AND
	wp_posts.post_status = 'publish'
AND
	wp_postmeta.meta_key = '_prefix_some_key'
AND (
	wp_postmeta.meta_value = 'Firs'
OR
	wp_postmeta.meta_value = 'Second'
OR
	wp_postmeta.meta_value = 'Third'
)

## Update menu_order for posts with specific meta value ##

UPDATE
	wp_posts
INNER JOIN
	wp_postmeta
ON
	wp_posts.ID = wp_postmeta.post_id
SET
	wp_posts.menu_order = '1'
WHERE
	1=1
AND
	wp_posts.post_status = 'publish'
AND
	wp_postmeta.meta_key = '_prefix_some_key'

AND (
	wp_postmeta.meta_value = 'First'
OR
	wp_postmeta.meta_value = 'Second'
OR
	wp_postmeta.meta_value = 'Third'
)
LIMIT
  100
  
## Get unique meta values ##

SELECT DISTINCT
	wp_postmeta.meta_value
FROM
	wp_postmeta
WHERE
	wp_postmeta.meta_key = '_prefix_some_key'