SELECT p.ID as order_id, p.post_date, max( CASE WHEN pm.meta_key = '_billing_email' and p.ID = pm.post_id THEN pm.meta_value …
mysql
Delete orphaned post meta
DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL …
Get a list of orphaned post meta
SELECT * FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL …
MySQL UPDATE and REPLACE
UPDATE wp_posts SET post_content = REPLACE( post_content, 'http://mysite.com', 'https://mysite.com' ), guid = REPLACE( guid, …
MySQL delete records in one table that do not exist in another table
DELETE FROM content_to_tags WHERE NOT EXISTS ( SELECT * FROM content WHERE content_id = content_to_tags.content_id ) …
MySQL update column in one table with data from another table
update table1 t1 join table2 t2 on t2.field = t1.field set t1.field1 = t2.matchingfield where t1.whatever = t2.whatever …
MySQL copy table data to a new table
CREATE TABLE backup_table LIKE existing_table; INSERT backup_table SELECT * FROM existing_table; …
Find duplicates values in a MySQL table
SELECT col, COUNT(col) FROM table_name GROUP BY col HAVING COUNT(col) > 1; …
Delete orphaned post meta in WordPress database
DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL …