Update any embedded attachments/images that use http. This one updates the src attributes that use double quotes:
UPDATE wp_posts
SET post_content = REPLACE(post_content, 'src="http://www.yoursite.com', 'src="https://www.yoursite.com')
WHERE post_content LIKE '%src="http://www.yoursite.com%';
This one takes care of any src attributes that use single quotes:
UPDATE wp_posts
SET post_content = REPLACE(post_content, 'src='http://www.yoursite.com', 'src='https://www.yoursite.com')
WHERE post_content LIKE '%src='http://www.yoursite.com%';
Update any hard-coded URLs for links. This one updates the URL for href attributes that use double quotes:
UPDATE wp_posts
SET post_content = REPLACE(post_content, 'href="http://www.yoursite.com', 'href="https://www.yoursite.com')
WHERE post_content LIKE '%href="http://www.yoursite.com%';
This one updates the URL for href attributes that use single quotes:
UPDATE wp_posts
SET post_content = REPLACE(post_content, 'href='http://www.yoursite.com', 'href='https://www.yoursite.com')
WHERE post_content LIKE '%href='http://www.yoursite.com%';
Update any “pinged” links:
UPDATE wp_posts
SET pinged = REPLACE(pinged, 'http://www.yoursite.com', 'https://www.yoursite.com')
WHERE pinged LIKE '%http://www.yoursite.com%';
This step is just a confirmation step to make sure that there are no remaining http URLs for your site in the wp_posts table, except the GUID URLs. You must replace WP_DB_NAME, near the beginning of the query, with the name of your database.
This will confirm that nowhere in the wp_posts table is there a remaining http URL, outside of the GUID column. This ignores URLs in the GUID column.
This query only searches; it does not replace anything, nor make any changes. So, this is safe to run. It’s a safe and quick way to check the wp_posts table while ignoring the guid column.
This SQL query should return an empty set. That would mean that it found no http URLs for your site. (This is all just 1 query. It’s 1 very, very long line.)
Remember to replace WP_DB_NAME, near the beginning of the query, with the name of your database.
SELECT * FROM WP_DB_NAME
.wp_posts
WHERE (CONVERT(ID
USING utf8) LIKE '%%http://www.yoursite.com%%' OR CONVERT(post_author
USING utf8) LIKE '%%http://www.yoursite.com%%' OR CONVERT(post_date
USING utf8) LIKE '%%http://www.yoursite.com%%' OR CONVERT(post_date_gmt
USING utf8) LIKE '%%http://www.yoursite.com%%' OR CONVERT(post_content
USING utf8) LIKE '%%http://www.yoursite.com%%' OR CONVERT(post_title
USING utf8) LIKE '%%http://www.yoursite.com%%' OR CONVERT(post_excerpt
USING utf8) LIKE '%%http://www.yoursite.com%%' OR CONVERT(post_status
USING utf8) LIKE '%%http://www.yoursite.com%%' OR CONVERT(comment_status
USING utf8) LIKE '%%http://www.yoursite.com%%' OR CONVERT(ping_status
USING utf8) LIKE '%%http://www.yoursite.com%%' OR CONVERT(post_password
USING utf8) LIKE '%%http://www.yoursite.com%%' OR CONVERT(post_name
USING utf8) LIKE '%%http://www.yoursite.com%%' OR CONVERT(to_ping
USING utf8) LIKE '%%http://www.yoursite.com%%' OR CONVERT(pinged
USING utf8) LIKE '%%http://www.yoursite.com%%' OR CONVERT(post_modified
USING utf8) LIKE '%%http://www.yoursite.com%%' OR CONVERT(post_modified_gmt
USING utf8) LIKE '%%http://www.yoursite.com%%' OR CONVERT(post_content_filtered
USING utf8) LIKE '%%http://www.yoursite.com%%' OR CONVERT(post_parent
USING utf8) LIKE '%%http://www.yoursite.com%%' OR CONVERT(menu_order
USING utf8) LIKE '%%http://www.yoursite.com%%' OR CONVERT(post_type
USING utf8) LIKE '%%http://www.yoursite.com%%' OR CONVERT(post_mime_type
USING utf8) LIKE '%%http://www.yoursite.com%%' OR CONVERT(comment_count
USING utf8) LIKE '%%http://www.yoursite.com%%');
Now, we move to the wp_comments table. This changes any comment author URLs that point to the http version of your site. This is in case you’ve ever replied to a comment while your URL was pointing to http.
UPDATE wp_comments
SET comment_author_url = REPLACE(comment_author_url, 'http://www.yoursite.com', 'https://www.yoursite.com')
WHERE comment_author_url LIKE '%http://www.yoursite.com%';
This updates the content of the comments on your site. If there are any links in the comments that are linking to an http URL on your site, they will be updated to https.
UPDATE wp_comments
SET comment_content = REPLACE(comment_content, 'http://www.yoursite.com', 'https://www.yoursite.com')
WHERE comment_content LIKE '%http://www.yoursite.com%';
Now we move to the wp_postmeta table. This takes care of any custom post meta that points to the http version of your site.
UPDATE wp_postmeta
SET meta_value
= REPLACE(meta_value, 'http://www.yoursite.com', 'https://www.yoursite.com')
WHERE meta_value LIKE '%http://www.yoursite.com%';
Now we move to the wp_options table. Update the “WordPress Address (URL)” and “Site Address (URL).” You can simply change these in the WordPress dashboard –> Settings page, rather than with these queries. You can just go to the Settings page and change both of these fields so that they start with https. But, here are the queries if you prefer to do it this way.
For the WordPress Address URL, you may have to modify www.yoursite.com. If you have WordPress installed in some other directory, then modify this according to your own WordPress URL. For example, some people have WordPress installed in a subdirectory named “blog” and so their WordPress Address would be https://www.yoursite.com/blog.
UPDATE wp_options
SET option_value
= "https://www.yoursite.com" WHERE wp_options
.option_name
= 'siteurl';
This one will update the Site Address URL (this is the home page of your site):
UPDATE wp_options
SET option_value
= "https://www.yoursite.com" WHERE wp_options
.option_name
= 'home';
src: https://isabelcastillo.com/mysql-wordpress-http-to-https