Cleanup orphaned ACF data

gistfile1.sql

# This might ruin your database and I do not take any responsibility for that. Backup your database before continuing
!
# Check the results throughly

SELECT * FROM `wp_postmeta` 
	WHERE `meta_key` IN 
		( SELECT TRIM(LEADING '_' FROM `meta_key`) AS mk 
			FROM `wp_postmeta` 
			WHERE `meta_value` regexp '^field_[0-9a-f]+' 
				AND `meta_value` NOT IN 
					(SELECT `post_name` FROM `wp_posts` WHERE `post_type` = 'acf-field') 
		) 
		OR `meta_key` IN 
		( SELECT `meta_key` AS mk 
			FROM `wp_postmeta` 
			WHERE `meta_value` regexp '^field_[0-9a-f]+' 
				AND `meta_value` NOT IN 
					(SELECT `post_name` FROM `wp_posts` WHERE `post_type` = 'acf-field') 
		)


# Apply delete

DELETE FROM `wp_postmeta` 
	WHERE `meta_key` IN 
		( SELECT TRIM(LEADING '_' FROM `meta_key`) AS mk 
			FROM `wp_postmeta` 
			WHERE `meta_value` regexp '^field_[0-9a-f]+' 
				AND `meta_value` NOT IN 
					(SELECT `post_name` FROM `wp_posts` WHERE `post_type` = 'acf-field') 
		) 
		OR `meta_key` IN 
		( SELECT `meta_key` AS mk 
			FROM `wp_postmeta` 
			WHERE `meta_value` regexp '^field_[0-9a-f]+' 
				AND `meta_value` NOT IN 
					(SELECT `post_name` FROM `wp_posts` WHERE `post_type` = 'acf-field') 
		)

Unfortunately when applying delete mysql error - #1093 - You can't specify target table 'wp_postmeta' for update in FROM clause - any thoughts on how to fix? Thanks.

Used
FROM (SELECT * FROM wp_postmeta) as pm
for lines 8, 15, 27, 34

@pelmered any idea about @mikeStapes question?

@pelmered any idea about @mikeStapes question?

No, not really. Sorry.
I haven't touched this for over five years. I guess this error is because of newer MySQL versions.

But try what ziembatron suggested. You probably have to add pm. before any column references to the wp_postmeta table as well.